April 5, 2012 at 11:15 am
Yes, I realize should be using SYSDATETIME() instead of GETDATE(). However, what is confusing me is that running the below, sometimes the 2nd value (the GETDATE()) is a SMALLER date than SYSDATETIME(). Does anyone know why?
--sql...
select SYSDATETIME() as [SYSDATETIME]
select GETDATE() as [GETDATE]
--here are some example results... notice how the 2nd statement returned is SMALLER than the first in the results!
--ex set 1.
--2012-04-05 11:10:49.0418009
--2012-04-05 11:10:49.040
--ex set 2.
--2012-04-05 11:14:30.0081269
--2012-04-05 11:14:30.007
April 5, 2012 at 11:22 am
SYSDATETIME()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
GETDATE()
returns datetime
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE.
All of the above comes directly from Books Online.
The precision of datetime is to 3ms.
April 5, 2012 at 11:24 am
http://msdn.microsoft.com/en-us/library/ms187819(v=sql.105).aspx Yup, datetime rounds to the nearest .000, .003, or .007 seconds.
April 5, 2012 at 11:51 am
ok... that *almost* makes sense (the rounding that occurs)... although I am struggling with this result that we had...
2012-04-05 09:59:06.0790269
2012-04-05 09:59:06.077
...i.e., does it make sense that something very close (i.e. would be a bit higher) to 2012-04-05 09:59:06.0790269 would get rounded to 2012-04-05 09:59:06.077? I would have thought based on Microsoft's rules it would then get rounded to 2012-04-05 09:59:06.080... but, then again, if is an "equivalent to Friday" today for me so perhaps I am not seeing it... and sorry for not posting this result earlier since the other ones *do* make sense with the documentation / responses provided.
April 5, 2012 at 12:22 pm
Keep in mind that there can be slight differences, especially at the highest precision point, simply due to CPU sequentiality constraints.
MaxDOP 1 might change how that behaves, but also might not, depending on the actual CPU instruction-sets being called. Accuracy at the point of max precision in any calculation is iffy at best, and a violation of basic mathematic principles in many/most cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2012 at 12:29 pm
Thanks everyone!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply