Along with the new data types DATE, TIME and DATETIME2 in SQL Server 2008, new and enhanced functions are also included.
These functions 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 code examples using the above 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