TIME/DATE Datatypes Accuracy.

Question

TIME/DATE Datatypes Accuracy.

SQL*

SSCertifiable

Points: 6638
More actions
January 27, 2013 at 11:18 pm

#267713

Hi all,
What is the meaning of "Accuracy of 100 nonseconds" in case TIME data type in sql server 2008.
And for every date/time data type there is an Accuracy constraint what does it means?
Thanks.
🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

A nanosecond (ns) is one billionth of a second (10-9 or 1/1,000,000,000 s)...( From Wikipedia http://en.wikipedia.org/wiki/Nanosecond )

As an example, a variable with data type TIME may store a data like 08:31:50.0000005 which means the time now is 08 Hours, 31 Minutes, 50 Seconds and 500 NanoSeconds

Similarly, I hope you can understand the meaning of other accuracy constraints for TIME/DATE data types.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQL* SSCertifiable Points: 6638 More actions · Answer 2

Kingston Dhasian (1/28/2013)
A nanosecond (ns) is one billionth of a second (10-9 or 1/1,000,000,000 s)...( From Wikipedia http://en.wikipedia.org/wiki/Nanosecond )
As an example, a variable with data type TIME may store a data like 08:31:50.0000005 which means the time now is 08 Hours, 31 Minutes, 50 Seconds and 500 NanoSeconds
Similarly, I hope you can understand the meaning of other accuracy constraints for TIME/DATE data types.

Thanks for ur reply,

But can u say it in words, in the above example 5*100 is the nanoseconds?

Like that if accuracy is 1 day, ex: 28th, jan, 2013, then 28*1 = 28...so on

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It means the time reported is accurate to 100 nanoseconds. You won't get any time values less than that reported.

If you need more information you may want to try looking up the TIME datatype in the SQL Server Books Online. It should be able to provide you with additional information.

SQL* SSCertifiable Points: 6638 More actions · Answer 4

Lynn Pettis (1/28/2013)
It means the time reported is accurate to 100 nanoseconds. You won't get any time values less than that reported.
If you need more information you may want to try looking up the TIME datatype in the SQL Server Books Online. It should be able to provide you with additional information.

Thanks for ur replay,

But i'm not able to catch the exact point of "Accuracy", and i have looked into the MSDN but there is also no such information on.

please clarify me.

🙂

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Are you familiar with fractions of time?

10th of a second

100th of a second

1000th of a second

etc.

Well a nano second is 1 billionth of a second.

So time is actuate to 100 billionth's of a second

SQL* SSCertifiable Points: 6638 More actions · Answer 6

anthony.green (1/29/2013)
Are you familiar with fractions of time?
10th of a second
100th of a second
1000th of a second
etc.
Well a nano second is 1 billionth of a second.
So time is actuate to 100 billionth's of a second

Now i got clear picture, as follows:

1sec = 10^9 nanoseconds

But in sql server the time's precession is 10^7, so 10^9 - 10^7 = 10^2 (100 is the part of the nanoseconds). For example as one of our author mentioned in this chain 12.12.34.0000005 this is 000000500 (500) nanosecond. I believe it is correct.

🙂