converting date time field

Hi,

I'm trying to report on the time records are created , but not to include the date. So for a month long period, I want to know how many records have been created between 8am & 9am. I can group the records and display by hour, but as the database is a time date field, it displays for each date as well.

I think I probably need to create a formula that will strip out the date information, then I can group by hour and that will return what I need, but I have no idea how create such a formula...

Any ideas?

Thanks,

Matt.What is your Database?
Write a Stored Procedure having the query group by Time and use that sp to design the report

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

convert varchar to decimal

When running this query, several records are returned but then sql server gives the follwing error. Can you see how it can be solved please? Thanks

select
case
when isnumeric([Column 9]) = 1 then convert(decimal(24, 4), [Column 9])
else
null
end
from
tblCEMTradeFeed

Error:

Error converting data type varchar to numeric.

You can not trust function ISNUMERIC a 100%. This function returns 1 also for values like '.', '2E3' (scientific notation), '+', '-', but not all of them can be conevrted to numeric data type.

select cast('2E3' as float)

go

select cast('2E3' as numeric(5, 2))

go

What is wrong with IsNumeric()?

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

AMB

|||How do I fix the sql then please?|||

The following expression might fix a issue,

Note:

Is numeric function will return true for “.”, “$” & etc.

Code Snippet

case when

isnumeric([Column 9]) = 1

and patindex('%[0-9]%',[column 9]) <> 0

then convert(decimal(24, 4), [Column 9])

else

null

end

|||

Hi Manivannan.D.Sekaran,

I guess the OP is expecting a decimal separator in the data, if not, why to convert to numeric(24, 4).

I think that the expression:

> and patindex('%[0-9]%',[column 9]) <> 0

will not yield the expected result. The following value will cause an error '2E3'.

select

case

when isnumeric([Column 9]) = 1 and patindex('%[0-9]%',[Column 9]) <> 0 then convert(decimal(24, 4), [Column 9])

else null end

from

(select '2E3' as [Column 9]) as t

go

AMB

|||

Did you check the link I attached to the post?

AMB

|||

Hi HUNCHBACK (padern me i can't able to get your orginal name),

Yes I agree with you. I hope STR should be a right choice to convert the string to decimal

Code Snippet

select

[Column 9],

Case When

patindex('%[0-9]%',[Column 9])<>0

Then

Case When

Isnumeric(str(replace([Column 9],'$',''),24,4)) =1

Then Cast(str(replace([Column 9],'$',''),24,4) as Numeric(24,4))

End

End

from

(

select '2E3' as [Column 9]

Union All

select '2E24' as [Column 9]

Union All

select '2E80' as [Column 9]

Union All

select '100' as [Column 9]

Union All

select '.' as [Column 9]

Union All

select '$' as [Column 9]

Union All

select '$9' as [Column 9]

Union All

select '878.8373738' as [Column 9]

Union All

Select 'mani'

) as t

|||

Hi Manivannan.D.Sekaran,

Much better. Try adding:

(

...

union all

select '$9'

) as t

AMB

convert rows into columns

i have a table which gives me a list of months as records...
ie..columnname : month
row1: jan
row2: feb
row 3: mar
and so on..
i want to make a column..where these months appear as column name
ie...column1: jan
column2: feb
column3: mar
also...what i want is if in the first table...any month is added as a row... the second table should also take the new month as a new column...
let me know asap
regards
Nikhil
MSKB (support.microsoft.com) article : 175574
Anith

Convert Punctuation to Spaces?

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> Hi,
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
> Thanks!

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @.SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:406e7002$1_1@.news.bluewin.ch...
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@.SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@.newsread1.news.pas.earth link.net...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:406e7002$1_1@.news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @.SearchIncludes) ')
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
> Thanks!

This may help:

http://oldlook.experts-exchange.com...Q_20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon|||> This may help:
>
http://oldlook.experts-exchange.com...Q_20711909.html
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!|||>"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
"Simon Hayes" <sql@.hayes.ch> replied in message
news:406e7002$1_1@.news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@.Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @.Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert getdate to week and day of week

I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?

Please help..Look up the DATEPART() function.

Convert Float to Decimal errors

I'm trying to move records from a SQL table with a float column to a DB2 database that has the column defined as Decimal (8,2) It keeps crashing saying it has a type mismatch problem. I tried changing my source command to pass in the column already converted and it still crashes on this. I also tried doing a data conversion task to do the conversion and I still get the same error. Any ideas?

Hi,

What you can do is, convert the field to 'varchar' type while reading from sql server and then change it to 'Decimal' while loading into DB2.

|||I'll give that a try. Thanks

Convert field

I am having a bit of a problem. I have a date field that is comming from
a different platform as a float. I need to convert these records to
datetime or small datetime. Is their a Stored Procedure to do this in SQL
or what is the quickest way to do this?
Thanks>> I have a date field [sic] that is comming from a different platform as a floa
t. <<
You don't know that a column is not anything like a field!
You don't know that a row is not anything like a record!
You might be able to do a CAST(foobar AS DATETIME). the quickest way
is to keep temporal data in temproal columns.|||You need to find out the starting point, for inhstance, what does 0.5
mean? Noon of what day?
Suppose 0 means midningt of January 1st, 2000.
Use dateadd function to add days (the integer part of the float) to
that start date
Then use dateadd to add seconds|||I have to suggestions.
1. The code which is performing the transformation from the other platform
may be able to convert as it goes.
2. You can Cast the float to a datetime list this...
Declare @.Mydate Float
Set @.Mydate = Cast( 1.125 as Float )
Select Cast( @.Mydate as DateTime)
However, it will require that the float from the other platform is using 0
as the same Epoch of 1900-01-01 00:00:00.000 .
Regards
Colin Dawson
www.cjdawson.com
"JimS" <noholycowsspam@.ya_NoJunk_hoo.com> wrote in message
news:O1Al2EOXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I am having a bit of a problem. I have a date field that is comming from
> a different platform as a float. I need to convert these records to
> datetime or small datetime. Is their a Stored Procedure to do this in SQL
> or what is the quickest way to do this?
> Thanks
>

Convert DB2 "FOR UPDATE" in SQL Server

Hello,
In DB2, we can do FOR UPDATE selection, in order to lock the records until the transaction is completed. e.g.
select field1, field2, field3
from table
FOR UPDATE;
How can I perform the equivalent feature in SQL Server?
Thanks!This is an exceptionally bad way to do things. It allows the "lunchtime lock" where a user has data locked for ages, preventing others from getting any real work done. The best way to simulate this feature in SQL Server is to retrieve the data, then turn off or unplug the network cable from the server.

-PatP|||Pat...Pat...Pat...

You've worked on big iron before....|||You've worked on big iron before....I know, I'll admit it... but I keep trying to prevent those bad habits from spreading!

-PatP|||I sincerely need helps for this question. And I am not fooling around here.

convert date issue in view

i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:

CREATE VIEW [billy.bhuj].[bo current month]

AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]

FROM [dbo].[bo]

Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))

the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.

hi,

are your trying to do something like a parameterized view

well if you are you should be creating stored procedure

rather than a view

regards,

joey

|||

sorry, my bad...

i re checked the results without the "where" clause, and i was getting an error code further down the result list! same Arithmetic Overflow" error! i removed all the "convert" lines and it runs okay.

bo.queue_date is a nvarchar. i am using "convert" to change it to a date field. i do not own the table where bo.queue_date originates.

so how do i convert from a nvarchar field to a date field, and then filter out the dates i don't want in the "where" clause? should i be using "cast" instead of "convert"? or could this be a result of improper data in the bo.queue_date of the original table?

i am not trying to pass a parameter, just filter the main table down to a more manageable size by filtering by current month and year. if a stored proc makes more sense to do this though, i could try that instead...

|||

How do you know if the values in queue_date can be successfully converted to datetime? Is it always in one particular format? Why is that column nvarchar instead of datetime or smalldatetime? You could do check like below and then convert to prevent the conversion error:

select ...

, year(t.q_dt)

, month(t.q_dt)

from (

select ...

, case isdate(bo.queue_date) when 1 then convert(datetime, bo.queue_date, 104) end as q_dt

from bo

) as t

where year(t.q_dt) = year(CURRENT_TIMESTAMP)

But I don't think this is the problem. You talked about arithmetic overflow error which is different. Is bo a table or view? If bo is a view then you need to check the SELECT statement of the bo to see if there are any expressions that can result in arithmetic overflow. Also, please post the exact error message when asking for help.