SQL SERVER – 2008 – Find Current System Date Time and Time Offset

SQL Server 2008 can return the system date time together with its time offset from GMT. If you want to find current datetime in SQL Server I suggest to read the following post :

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

This post is related to new feature available in SQL Server 2008. In SQL Server 2008 there is a function which provides current offset of the system from GMT time as well. Basically it shows the system datetime with offset. I think this can be useful in some of the instances where SQL Server are depending on the time offset.

SELECT SYSDATETIMEOFFSET() AS 'Windows System Time'
GO

When the Time Offset Really Matters

The offset you see is the offset of the Windows server where SQL Server runs, not the offset of the person running the query. If your server sits in one country and your users sit in another, the value will follow the server clock. I have seen people confused by this more than once when they test from a laptop in a different city. Remember this before you blame the function for a wrong hour.

This matters most when an application writes data from many locations. A plain DATETIME value does not remember which zone it came from. A DATETIMEOFFSET value keeps the offset together with the date and time, so you can always tell the exact moment a row was written. Daylight saving time also changes the offset of many servers twice a year, which is one more reason to store it.

SQL Server 2008 added a few friends of this function that are worth knowing:

• SYSDATETIME() returns the local server time as DATETIME2, with more precision than GETDATE().
• SYSUTCDATETIME() returns the current UTC time.
• SWITCHOFFSET() shows a DATETIMEOFFSET value in another offset.
• TODATETIMEOFFSET() attaches an offset to a value that has none.

My simple habit is to store UTC or DATETIMEOFFSET values in tables and convert to local time only when showing data to users. It keeps reports honest when your users live in more than one place. It also makes comparing events from servers in different countries much easier.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.