SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do you know your getdate()?


Do you know your getdate()?

Author
Message
JacekO
JacekO
SSC Eights!
SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)

Group: General Forum Members
Points: 888 Visits: 615
Comments posted to this topic are about the item Do you know your getdate()?

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

ziangij
ziangij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3278 Visits: 377
thanks :-)... was really not aware of this stuff... also a good interview question...
brian.neumeier-603907
brian.neumeier-603907
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 740
Is there a reason you chose not to perform the same tests on the GETUTCDATE function?
bhuvii
bhuvii
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
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
Kari Suresh
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 373
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
uhlmann
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 15
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
peter.row
SSChasing Mays
SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)

Group: General Forum Members
Points: 623 Visits: 412
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
Daniel Tonagel
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 23
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
hakan.borneland
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 115
... 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
matthieu.ravard
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 21
is the other instructions present in the code takes some millisecond ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search