March 30, 2009

UTC or GMT time in SQL Server 2005/2008




SQL Server 2005/2008 has a GETUTCDATE() function that retreives the local machine's date and time without the time zone offset, representing the current UTC time (Coordinated Universal Time) or GMT (Greenwich Mean Time).

If you need a more fractional seconds precision, use the SYSUTCDATETIME instead of GETUTCDATE()

Let's see the results when running some common date and time functions in SQL Server:


SELECT GETDATE() - 2009-03-28 13:08:37.553


SELECT SYSDATETIME() - 2009-03-28 13:08:37.5570000


SELECT GETUTCDATE() - 2009-03-28 07:38:37.557


SELECT SYSUTCDATETIME() - 2009-03-28 07:38:37.5570000


SELECT SYSDATETIMEOFFSET() - 2009-03-28 13:08:37.5570000 +05:30




As you can observe, the SYSDATETIME and SYSUTCDATETIME are used to display time with fractional second precision. The SYSDATETIMEOFFSET displays the time zone offset.

To find a difference between the Local time and UTC time use this query:


SELECT DATEDIFF(MINUTE,GetDate(),GetUTCDate());




Note: You may also want to checkout the SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET functions


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "UTC or GMT time in SQL Server 2005/2008"
  1. Bharat Bhushan said...
    February 3, 2011 at 3:06 AM

    no comments please

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions