System clock access from mssql 2000

Question

System clock access from mssql 2000

Yuren Deokaran

SSC Rookie

Points: 30
More actions
August 2, 2005 at 6:15 am

#168100

I'm looking for a way to access the system clock from within SQL Server 2000. In particular, I'm interested in the nanosecond portion. I can see that one is able to extract the milliseconds using Datepart(ms, Getdate()) but i'm looking for greater precision. The [initial] reason for this is that I'm attempting to generate random numbers and the system I am using is able to make many 100's of rand() calls within the same millisecond thereby limiting its value as a seed. I have now solved this problem using a counter that I grow exponentially but am still interested in accessing the system clock. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

pg53 Ten Centuries Points: 1303 More actions · Answer 1

Hi Yuren -

AFAIK it's not possible to resolve the time to anything smaller than milliseconds - certainly not nanoseconds - I guess you meant micro- but my understanding is that's equally out of reach. Have a look on MSDN for high resolution timer

If anyone can put me right on this, though, I'd like to know - I've had a couple applications that would benefit.

Also if I understand you correctly, the high frequency of calls to rand() shouldn't matter; normally you would only use a seed to initialise, so the fact that multiple subsequent calls were within the same ms wouldn't make any difference

pg

Yuren Deokaran SSC Rookie Points: 30 More actions · Answer 2

Thanks pg53. I was using rand incorrectly. If anyone's interested, converting/casting the date to a float yields something that seems to have microsecond precision. Have a look at "select convert(float,getdate())". Not exactly sure what the result is though - looks like 38565.447765277779.

pg53 Ten Centuries Points: 1303 More actions · Answer 3

Hi Yuren

from BOL:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

I expect getdate() returns the same format

pg