GetDate() V/s GetUTCDate()

Today my colleague asked me, what is the difference between GetDate() and GetUTCDate()? and I thought it would be a nice idea to blog about it. So here it is, my next article.

In simple words the GetDate() returns the local time where as GetUTCDate() returns the GMT time.

GETDATE()

  • Returns the current database system timestamp as a datetime value.
  • Does not contain the time zone offset.
  • The datetime value is derived from the host operating system where the SQL Server is running.

GETUTCDATE()

  • It will return the GMT date and time (Greenwich Mean Time).
  • GETUTCDATE () can be used to store the timestamp that is independent of time zones.
  • The datetime value is derived from the local time and the time zone of the host operating system.

This simple code shows the difference in the GetDate() and GetUTCDate().

DECLARE @Date DATETIME = GETDATE(),
 @UTCDate DATETIME = GETUTCDATE();

WITH CTE
AS (
 SELECT @Date AS SYSDATE,
  @UTCdate AS UTCDATE,
  DATEDIFF(MINUTE, @utcdate, @date) AS DIFF
 )
SELECT 'LocalTime' = CONVERT(VARCHAR(20), SYSDATE),
 'GMTTime' = CONVERT(VARCHAR(20), UTCDATE),
 'TimeZone' = CONVERT(VARCHAR(20), CONVERT(VARCHAR(10), (Diff) / 60)) + ':' + 
            CONVERT(VARCHAR(2), (ABS(Diff)) % 60)
FROM CTE;

Here is the output, the LocaTime shows the server time where as the GMTTime is derived from the local time and the time zone of the host server.

GetUTCDate_1

Both GetDate() and GetUTCDate() are non-deterministic functions and there are limitation around indexing the columns using there functions. Have a look at BOL as further references.

References
http://msdn.microsoft.com/en-IN/library/ms188383.aspx
http://msdn.microsoft.com/en-IN/library/ms178635.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *