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


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