SQL2000 - Two different DateTime values return equal

Can anyone tell me why it is that Sql Server 2000 returns 1 on the this?:

Declare @firstDate DateTime
Select @firstdate = '6/15/2006'
Select 1 where '6/14/2006 23:59:59.999' = @firstDate

These are two separate DateTime values, but yet SQL Server 2000 seems to think they are equal... This threw a reporting query of mine off simply because the statement returns true in my match criteria.
LVL 9
Sean McIlvennaSr. Software ArchitectAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Hi realevergod,

The datetime data type does not have the ability to resolve differences larger than 0.03 seconds, so
6/14/2006 23:59:59.999 rounds up to 6/15/2006 0:00:00

Regards,

Patrick
0
 
Patrick MatthewsCommented:
> resolve differences larger than 0.03 seconds

Sorry, meant SMALLER than :)

Regards,

Patrick
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I could reproduce your problem, I went even a step further (no solution):

Declare @firstDate DateTime
Select @firstdate = convert(datetime,'6/15/2006',101)
declare @lastdate datetime
select @lastdate = convert(datetime, '2006-06-14 23:59:59.999', 121)
Select 1 where @lastdate = @firstDate
select convert(varchar(30), @firstdate, 121), convert(varchar(30), @lastdate, 121)

Listening & Learning
0
 
Patrick MatthewsCommented:
realevergod,

Sorry, the smallest resolution is not 0.03 seconds, but rather 1/300th of a second, or about 0.00333 seconds.
But SQL Server still has to round yours up.

Regards,

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.