Do you know your getdate()?

Question

Post reply

Do you know your getdate()?

JacekO

SSCertifiable

Points: 6610
More actions
May 11, 2010 at 8:12 pm

#220915

Comments posted to this topic are about the item Do you know your getdate()?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Viewing 15 posts - 1 through 15 (of 63 total)

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

thanks :-)... was really not aware of this stuff... also a good interview question...

brian.neumeier-603907 Old Hand Points: 356 More actions · Answer 2

Is there a reason you chose not to perform the same tests on the GETUTCDATE function?

bhuvii Grasshopper Points: 15 More actions · Answer 3

Testing the precision of GETDATE()...

instead of getting values 595 and 15.6521,

i got

4262 -out of 10 million calls i got distinct 4262 values returned by GETDATE()

and

3.33318 - what is the precision of the GETDATE() function

which is supposed to be true.

i ran the given query in the post, which the answer is supposed to be true.

i ran the query against SQL SERVER 2008

if i am wrong somewhere, let me know

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 4

Buddies,

I am using SS2005 and I ran the first query: DECLARE @TimeStart DATETIME

DECLARE @Time DATETIME

DECLARE @TimeEnd DATETIME

DECLARE @I INT

DECLARE @Count INT

SET @I = 0

SET @Count = 0

SET @TimeStart = GETDATE()

SET @Time = @TimeStart

WHILE @I < 10000000

BEGIN

SET @TimeEnd = GETDATE()

IF @TimeEnd <> @Time

BEGIN

SET @Count = @Count + 1

SET @Time = @TimeEnd

END

SET @I = @I + 1

END

PRINT @Count

PRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)

I have got the values: 6519 and 3.33379.

I ran the same query again to re-verify, but I have got: 6518 and 3.3363.

The precision is 3.33 for GETDATE() function.

How these values are vary (595 and 15.6521 - 6519 and 3.33379) ?

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

uhlmann Grasshopper Points: 15 More actions · Answer 5

Same result ...

15020 and 3.3462

SQL 2005 Developer on Vista 64bit

Edit:

Same test on SQL 2008 Developer on the same machine

2900 and 15.5941

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 6

I ran the same 2 tests on Win 7 64bit Quad core, 8GB Ram machine running SQL Server 2008 Developer 64bit.

And got the following:

GetDate(): 2604 values with precision 3.33333 milliseconds

SysDateTime(): 13930 values with precision 1000 microseconds, (1 millisecond)

Judging by the rest of the comments so far it seems to be that XP can't hack it whilst latest OS' can (unless CPU cores played a part).

However in production you would never use XP, Vista or 7 to run your SQL Server so more interesting would be results on other Windows server versions.

Daniel Tonagel SSC Rookie Points: 39 More actions · Answer 7

Some more SYSDATETIME Results:

SQL 2008 R2 on Windows 7 x64: 1 ms

SQL 2008 R2 on Win 2008 R2 on HyperV on Win 2008 R2: 15.5 ms

SQL 2008 R2 on Win 2008 R2 on VMWare on Win 7 x64: 1.3 ms

hakan.borneland SSC Enthusiast Points: 198 More actions · Answer 8

... and another test

Win XP 32 bit SP3

SQL Server 2005 SP3 (9.0.4262)

CPU 2 processors (Core duo)

4517 and 3.32809

matthieu.ravard Grasshopper Points: 15 More actions · Answer 9

is the other instructions present in the code takes some millisecond ?

SQLZ SSChampion Points: 12872 More actions · Answer 10

I get 3.3 ms here using your own code.

The worst part about it is you would have not given the job to someone who correctly responded with 3.3ms for GETDATE() precision:-P

Karl
source control for SQL Server

adrian.buzila Ten Centuries Points: 1116 More actions · Answer 11

If you get 15.6521 it does not mean that this is the precision of getdate()!

Why do you think that @Time and @TimeEnd have succesiv values?

The conclusion, in my opinion, is the your system is very good (count is only 595 so only 595 times @Time and @TimeEnd are different, on my system is gets 18475), but the load of you system is very high, your processor is taken by another process from time to time so you get a very big value for the "precision" (in my case is 3.34344).

Daniel Tonagel SSC Rookie Points: 39 More actions · Answer 12

matthieu.ravard (5/12/2010)
is the other instructions present in the code takes some millisecond ?

Doesn't matter as long as you get several loop iterations before the timer gives another value.

wschampheleer SSCertifiable Points: 5504 More actions · Answer 13

WIN XP SP3 / SQL Server 2008 SP1 on dual core machine

getdate: 6043 3.3399 & 5957 3.33557

sysdatetime: 1713 15625 & 1789 15625

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Leendert van Staalduinen SSC Eights! Points: 877 More actions · Answer 14

My results on a single Windows 2000 Professional SP4 machine:

- In SQL Server 2000 Query Analyzer: did not run within 5 minutes runtime, I do not understand why.

- In SQL Server 2000 SP4, via Access .ADP file: 1,418 / 15.62,

- In SQL Server 2005 SP2, via Access .ADP file: 789 / 15.62.

Hope, this adds to the discussion, Leendert.