New Date and Time Functions in SQL Server 2008

Introduction

SQL Server 2008 introduces five new date and time functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET.

In this article described new functions in detail.

1) SYSDATETIME

The SYSDATETIME function basically returns the current system timestamp without the time zone information.

SELECT SYSDATETIME()

GO

SYSDATETIME

------------------------

2011-06-27 16:06:15.9409129

2) SYSDATETIMEOFFSET

The SYSDATETIMEOFFSET function is much similar to SYSDATETIME function; however it will also get you the time zone information.

SELECT SYSDATETIMEOFFSET()

GO

SYSDATETIMEOFFSET

-------------------------------

2011-06-27 16:07:01.8205935 +05:30

3) SYSUTCDATETIME

The SYSUTCDATETIME function returns the Greenwich Mean Time (GMT) or the Coordinated Universal Time (UTC). This time is derived from the current local system time and the time zone settings of the server where SQL Server 2008 is running.

SELECT SYSUTCDATETIME()

GO

SYSUTCDATETIME

------------------------

2011-06-27 10:38:25.6239407

4) SWITCHOFFSET

The SWITCHOFFSET function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.

SELECT SYSDATETIMEOFFSET(),

SWITCHOFFSET (SYSDATETIMEOFFSET(), '+00:00')

GO

SYSDATETIMEOFFSET SWITCHOFFSET

------------------------------- -------------------------------------

2011-06-27 16:30:55.7127120 +05:30 2011-06-27 11:00:55.7127120 +00:00

5) TODATETIMEOFFSET

The TODATETIMEOFFSET function returns a DATETIMEOFFSET value that is translated from a DATETIME2 expression.

SELECT TODATETIMEOFFSET (GETDATE(),'+05:30')

GO

TODATETIMEOFFSET

---------------------------------

2011-06-27 16:26:56.340 +05:30