February 26, 2008 at 11:59 pm
I am using SQL Server 2000.
when I am querying data from following query it gives an error.
Query: select * from SaleMain where dateinfo<='2008-02-25'
and dateinfo>='2008-02-25'
error: Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
And occour the same error for : select * from SaleMain where dateinfo<=cast('2008-02-25' as datetime)
and dateinfo>=cast('2008-02-25' as datetime)
But when I am using this query it works:
select * from SaleMain where dateinfo<=cast('2008-02-25 00:00:00.000' as datetime)
and dateinfo>=cast('2008-02-24 00:00:00.000' as datetime)
And then I try this query and works fine: select * from SaleMain where dateinfo<=cast('2008-02-25' as datetime)
and dateinfo>=cast('2008-02-25' as datetime)
And also this query works after that: select * from SaleMain where dateinfo<='2008-02-25'
and dateinfo>='2008-02-25'
Actually I am amazed with this functionality. And I have experience in this type of adhoc behavior for datetime in earlier also.
Can anyone please tell me, is there any sure and a very accurate way of writing these type of queries those are using datetime values.
🙂
February 27, 2008 at 1:21 am
Drop the hyphens.
WHERE SomeCol >= '20080225' AND SomeCol < '20080226'
to get all records dated february 25, 2008.
N 56°04'39.16"
E 12°55'05.25"
February 27, 2008 at 2:28 am
Dates, sadly, are not universally formatted. As Peso said, there's an ISO way of supporting dates which doesn't have the hyphens. If you want to both self-document your code and be absolutely sure of the handling of the dates, use the CONVERT function rather than CAST. With convert, you do something like
CONVERT(datetime, 'my date time string', x)
where x is a number you can obtain from looking in Books Online (SQL Server's manual / F1 help). In the help topic called "Cast and Convert" you'll see the various numbers for x that you can use. The value of x indicates the type of formatting you should apply to your date string. This way you can be sure you will always interpret the dates in a correct fashion.
{{BEGIN RANT}}
I really hate software that was obviously written and only ever run in the US market that doesn't handle dates correctly!!! 😛 mm/dd/yyyy (US) rather than dd/mm/yyyy (Australia and other places) don't always place nice and I couldn't bear to change my Windows system settings to work the other way. I'm not saying that the US are wrong for formatting their dates that way (although the ordering doesn't really make sense at all - days are within months within years or you could say years contain months that contain days but days shouldn't be in the middle) but if the programmer used the standard OS way of handling dates then there wouldn't be an issue...
{{END RANT}}
😀 :w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply