How accurate is GetDate() ?

[jim_geissman]

According to MS, GetLocalTime() (in C++) is only accurate to approx a
second,
even though it reports milliseconds, and calling it twice and computing
the
interval can on occasion lead to a negative interval.

Is T-SQL's GetDate() more accurate than that, or at least
non-decreasing?

Thanks,
Jim

[Hugo Kornelis]

On 26 Sep 2006 11:28:07 -0700, ji**********@co untrywide.com wrote:

>According to MS, GetLocalTime() (in C++) is only accurate to approx a
second,
even though it reports milliseconds, and calling it twice and computing
the
interval can on occasion lead to a negative interval.

Is T-SQL's GetDate() more accurate than that, or at least
non-decreasing?

Hi Jim,

Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTA MP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.

--
Hugo Kornelis, SQL Server MVP

[David Portas]

Hugo Kornelis wrote:

>
Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTA MP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.

Just to split hairs over this a little: The maximum precision of the
value returned by GETDATE() is 1/300th of a second. That doesn't
necessarily mean it is *accurate* to within 1/300th of a second
compared to some other time measurement such as the system clock.
However, like Hugo, I have never heard of any issue where serial
executions of GETDATE() went negative. It's not difficult to verify
that the time returned by GETDATE() is within a fraction of a second of
the system clock value returned by other means.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[--CELKO--]

>Is T-SQL's GetDate() more accurate than that, or at least non-decreasing? <<

Yes; read the other answers. The real answer is that the FIPS-127
tests require five or more decimal places of seconds in a timestamp for
Federal use. Maybe when we get to 64 bit hardware and a better
internal representation ..

[jim_geissman]

Thanks, fellows.

By way of experiment, I wrote a loop that wrote 100,001 GetDate()s to a
table,
with an identity column to record the sequence. That took ~2 sec on
this
server. Then I examined the 100,000 intervals. Their distribution:

Interval (sec) N
0 99855
..13 44
..16 100
..30 1

I conclude that on this particular SQL2000 server, the clock usually
ticks every
160 ms (but not always).

I did this a few times and never saw a negative interval, although
that's not proof
it could never happen.

Thanks again
Jim