When does the day end
-
Can anyone please tell me why
select convert(datetime, '2005-10-03 23:59:59.999', 121) returns '2005-10-04 00:00:00.000'
and
select convert(datetime, '2005-10-03 23:59:58.999', 121) returns '2005-10-03 23:59:59.000'
and
select convert(datetime, '2005-10-03 23:59:58.432', 121) returns '2005-10-03 23:59:58.433'
It may be my set up or this may be a known issue?
-
Hmmm ... the ever reliable Books Online reveals this,
datetime and smalldatetime
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
I believe the reason for the 3.33 milliseconds is something to do with the tick frequency of the CPU, or something along those lines.
--------------------
Colt 45 - the original point and click interface
-
Hence, since SQL cannot store dates with a finer granularity than 3ms, the day ends at 23:59:59.997
Other times which can be stored correctly include 23:59:59.993 and 23:59:59.990.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The easiest way (without worrying about internal time-storing architecture) to describe when the day ends (say 2005-10-03) is when it's less than the start of the next day...
So, '2005-10-03' starts when >= '2005-10-03' and ends when < '2005-10-04'
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply