Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

SYSDATETIME versus GETDATE

Modified: 2008/08/21 16:42 by vanthoog - Categorized as: SQL Server
In SQL Server 2005 (and previous versions) one would use GETDATE() to get the current date and time. In SQL Server 2008 this is still possible, but one can also use SYSDATETIME(). So what is exactly the difference?

The function GETDATE() returns the seconds part of the time in three fractions. The function SYSDATETIME() however returns the seconds part of the time in seven fractions. This is demonstrated in the following example:

select 'The current date and time is: ' , GETDATE()

select 'The current date and time is: ' , SYSDATETIME()

The current date and time is: 	2008-08-21 16:28:50.340
The current date and time is: 	2008-08-21 16:28:50.3406250

At first this may seem insignificant, but it is an important difference when using the data type DATETIME2. The data type DATETIME2 stores dates and times in a higher precious than the (old) data type DATETIME and therefore the difference between GETDATE() and SYSDATETIME() is important when using DATETIME2:

declare @dt table (bigdt datetime2)

insert into @dt 
values 
	( GETDATE() ) ,
	( SYSDATETIME() ) 

select * from @dt

2008-08-21 16:35:01.7130000
2008-08-21 16:35:01.7156250

Now what happens when you use SYSDATETIME() in combination with the (old and) less precious data types DATETIME and SMALLDATETIME? Fortunately no error is returned, but instead the additional fractions are ignored:

declare @dtold table (smalldt smalldatetime, normaldt datetime)

insert into @dtold 
values 
	( SYSDATETIME() , SYSDATETIME() ) 

select * from @dtold

2008-08-21 16:39:00	2008-08-21 16:38:51.810

So there is really no reason to keep on using GETDATE(). And therefore the simple advice is: in SQL Server 2008 always use SYSDATETIME() when you want to retrieve the current date and/or time.

And by the way, the same thing applies to GETUTCDATE() and SYSUTCDATETIME(). So the second advice is: in SQL Server 2008 do not use GETUTCDATE(), but always use SYSUTCDATETIME().

 © Evident Interactive BV