??? SQL Server 2000 leap year date comparision problem ????
-
Hi,
A greenhorn question
Try below:
create table t1(
d1 datetime,
d2 datetime)
insert into t1 values('20040101 00:00:00.000','20040131 23:59:00.000')
insert into t1 values('20040201 00:00:00.000','20040229 23:59:00.000')
select * from t1
Result:
2004-01-01 00:00:00.000 2004-01-31 23:59:00.000
2004-02-01 00:00:00.000 2004-02-29 23:59:00.000
THEN TRY:
select * from t1 where d2 <= '20040229'
Result:
2004-01-01 00:00:00.000 2004-01-31 23:59:00.000
If you try :
select * from t1 where convert(datetime,d2,101) <= convert(datetime,'20040229',101)
it will work.
WHY 2004-02-29 ROW IS NOT INCLUDED, is this the normal behaviour ?
Really appreciate your help
PS: this is valid for any other date with timepart >0
-
I would say that this is expected behaviour.
When you don't supply a time portion, SQL Server assumes 00:00:00.000 that is midnight.
When you change your query to
select * from t1 where d2 <= '20040229 23:59:00.000'
select * from t1 where d2 <= '20040301'
in both cases two rows should be returned
d1 d2
------------------------------------------------------ ------------------------------------------------------
2004-01-01 00:00:00.000 2004-01-31 23:59:00.000
2004-02-01 00:00:00.000 2004-02-29 23:59:00.000
(2 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It happens with any date not just for leap year. Frank's suggestion is correct.
-
If you wait (a little bit longer, as we can read today) for Yukon, you won't run into these problems anymore.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply