February 6, 2006 at 3:54 pm
I have been messing around with this for a while, but can't seem to figure it out. I am using a unix string in that looks like this: mm/dd/yyyy. I am attempting to use this string in a select statement as follows:
......
Where thisdate > convert(*******)
I can't seem to figure out the correct syntax for the ******. The format I need this in is 'mm/dd/yyyy 11:59:59:999'
Any help would be appreciated.
February 6, 2006 at 4:09 pm
I am a little uncertain of what your asking.
First Sql server stores dates as 2 integers, first int = days past 01/01/1900, and second int = milliseconds after midnight.
if your string is mm/dd/yyyy, can just put the string and it will be am implicit coversion. but it is better to explicitly cast.
Cast or Convert will work.
CAST ( expression AS data_type )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
so
where datefield > cast('02/06/2006 23:59:59:999' as datetime)
where datefield > convert(datetime, '02/06/2006 23:59:59:999')
will produce the same result. the Style is not required in this situation it is an optional parameter.
February 7, 2006 at 4:23 am
The Style doesn't work at all when converting from string to datetime, it's used only when converting from datetime to string.
Also, be aware of the language when dates are in such an ambigous format as mm/dd/yyyy - there's a risk of some unexpected results. (this format is both SET DATEFORMAT and SET LANGUAGE dependent)
set language us_english
select cast('02/06/2006 23:59:59:999' as datetime)
select convert(datetime, '02/06/2006 23:59:59:999')
set language British
select cast('02/06/2006 23:59:59:999' as datetime)
select convert(datetime, '02/06/2006 23:59:59:999')
Changed language setting to us_english.
------------------------------------------------------
2006-02-07 00:00:00.000
------------------------------------------------------
2006-02-07 00:00:00.000
Changed language setting to British.
------------------------------------------------------
2006-06-03 00:00:00.000
------------------------------------------------------
2006-06-03 00:00:00.000
The best would, if possible, be to not use a '/' delimited format at all, but instead yyyymmdd (unseparated), and as Ray says - never rely on implicit conversions - always do it explicitly.
/Kenneth
February 7, 2006 at 7:45 am
Thanks for the help guys. I have it working now. I guess I was a little confused as to exactly what the cast and covert functions did.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply