High Quality Online Courses on Special Sale - SQLNetHub

New Date-Related Functions in SQL Server 2008 and Later

by

Along with the new data types DATE, TIME and DATETIME2 in SQL Server 2008, new and enhanced functions are also included.

New Date Functions Added in SQL Server 2008 and Later

The new data functions originally added in SQL Server 2008 are :

  • SYSDATETIME
  • SYSUTCDATETIME
  • SYSDATETIMEOFFSET
  • SWITCHOFFSET
  • TODATETIMEOFFSET

A more thorough explanation follows:

SYSDATETIME
This function is like the GETDATE function with the difference that it returns the current date and time with the precision of DATETIME2.

SYSUTCDATETIME
This function returns the current date and time in UTC again with the precision of DATETIME2.

SYSDATETIMEOFFSET
This function returns the current date and time along with the system time zone as a DATETIMEOFFSET value.

For all the above functions, if desirable, you can cast its values to DATE or TIME.

SWITCHOFFSET
This function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC vaslue. In plain words it returns the date and time along with the current time offset.

TODATETIMEOFFSET
Even though it might seem similar to the SWITCHOFFSET function, the TODATETIMEOFFSET function has a different purpose.
It actually converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. So, if for an example you execute the following T-SQL statement:

SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET

…you will get a DATETIMEOFFSET value saying that the date is: 2009-07-29 and time: 20:00 and the time zone offset is +03:00.

The exact result of the above T-SQL statement is: 2009-07-29 20:00:00.0000000 +03:00

Another difference between SWITCHOFFSET and TODATETIMEOFFSET is that the latter is not restricted to a DATETIMEOFFSET as an input value rather it accepts any date and time value (like in the above example).

 

T-SQL Examples of Using the New Date-Related Functions in SQL Server 2008 and Later

The below examples, show how you can use the above data functions:

--SYSDATETIME (Returns the System date and time with DATETIME2 precision)
SELECT GETDATE() AS CURRENT_DATETIME_OLD, SYSDATETIME() AS CURRENT_DATETIME_NEW
SELECT (CAST (GETDATE() AS DATE)) AS CURRENT_DATE_OLD, (CAST (SYSDATETIME() AS DATE)) AS CURRENT_DATE_NEW
SELECT (CAST (GETDATE() AS TIME)) AS CURRENT_TIME_OLD, (CAST (SYSDATETIME() AS TIME)) AS CURRENT_TIME_NEW

--SYSUTCDATETIME (Returns the UTC date and time with DATETIME2 precision)
SELECT SYSUTCDATETIME() AS CURRENT_UTC_DATETIME
SELECT (CAST (SYSUTCDATETIME() AS DATE)) AS CURRENT_UTC_DATE
SELECT (CAST (SYSUTCDATETIME() AS TIME)) AS CURRENT_UTC_TIME

--SYSDATETIMEOFFSET (Returns the Date and time along with the current time offset)
SELECT SYSDATETIMEOFFSET() as CURRENT_DATE_TIME_OFFSET

--SWITCHOFFSET (Adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET

--TODATETIMEOFFSET (Converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset)
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(GETDATE(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(SYSDATETIME(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET

 

Learn More Tips like this – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,492