Sunday, February 28, 2010

SQL Server: GETDATE() & GETUTCDATE() & different time zones

Most of us will use GetDate() function for providing default value in SQL server columns. This function Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

This works perfectly if you don’t have to show reports and such stuffs for users from different time zones. In case you want to store time independent of time zones in some universal format; what will do? Well there is GetUtcDate() function for you. This function will return then UTC date based on the setting of the server on which SQL server is installed.

I executed the following function & I got the two different date output values.


SELECT  GETDATE() AS Expr1, GETUTCDATE () AS Expr2

2/28/2010 1:27:17 PM
2/28/2010 7:57:17 AM



SQL Server 2008
SQL Server 2008 has two new DataTypes: date & time

You can use them to retrieve the date & time part from the date time functions like the following

Current Date Value
SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

Current Time Value
SELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, SYSDATETIMEOFFSET())
    ,CONVERT (time, SYSUTCDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE())
    ,CONVERT (time, GETUTCDATE());
Post a Comment