Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Do you know your getdate()? Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2010 8:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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.
Post #920128
Posted Tuesday, May 11, 2010 10:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,061, Visits: 370
thanks ... was really not aware of this stuff... also a good interview question...
Post #920164
Posted Tuesday, May 11, 2010 11:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 6, 2014 2:03 PM
Points: 37, Visits: 653
Is there a reason you chose not to perform the same tests on the GETUTCDATE function?
Post #920165
Posted Tuesday, May 11, 2010 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 1:50 AM
Points: 1, 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
Post #920184
Posted Tuesday, May 11, 2010 11:58 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, 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
Post #920187
Posted Wednesday, May 12, 2010 1:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 24, 2011 6:08 AM
Points: 1, Visits: 14
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
Post #920206
Posted Wednesday, May 12, 2010 1:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:52 AM
Points: 221, Visits: 278
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.
Post #920216
Posted Wednesday, May 12, 2010 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 1:23 PM
Points: 7, 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
Post #920217
Posted Wednesday, May 12, 2010 1:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 5, 2013 2:14 AM
Points: 12, Visits: 112
... and another test

Win XP 32 bit SP3
SQL Server 2005 SP3 (9.0.4262)
CPU 2 processors (Core duo)

4517 and 3.32809
Post #920225
Posted Wednesday, May 12, 2010 1:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:39 AM
Points: 1, Visits: 21
is the other instructions present in the code takes some millisecond ?
Post #920226
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse