Date and Time Functions in SQL Server 2008

By: Editor
March 04, 2010

Introduction

SQL Server 2008 introduces five new date and time functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET. In this article we will take a look into each of these new functions in detail. 

SYSDATETIME

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

SELECT SYSDATETIME() AS [SYSDATETIME]
GO
SYSDATETIME
------------------------
2010-03-03 22:00:00.00

SYSDATETIMEOFFSET

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

SELECT SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET]
GO

SYSDATETIMEOFFSET
-------------------------------
2010-03-03 22:00:00.00 +05:30


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() AS [SYSUTCDATETIME]
GO

SYSUTCDATETIME
------------------------
2010-03-03 16:30:00.00

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() AS [SYSDATETIMEOFFSET]
	,SWITCHOFFSET (SYSDATETIMEOFFSET(), '-00:00') AS [SWITCHOFFSET]
GO
SYSDATETIMEOFFSET 			SWITCHOFFSET
-------------------------------		-------------------------------------
2010-03-03 22:00:00.00 +05:30 		2010-03-03 16:30:00.2343750 +00:00

The final step will be to enable encryption on the user database by executing the below mentioned TSQL code.

TODATETIMEOFFSET

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

SELECT TODATETIMEOFFSET (GETDATE(),'+05:30') AS [TODATETIMEOFFSET] 
GO

TODATETIMEOFFSET
---------------------------------
2010-03-03 22:00:00.000 +05:30

References: Date and Time Data Types and Functions

Conclusion

In this article you have seen some of the new Data and Time Functions in SQL Server 2008.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."