SQL SYSDATETIMEOFFSET Function

The SQL SYSDATETIMEOFFSET function is a Date Function, which returns the datetimeoffset (7) of the Current Date and Time of the computer on which the server instance is running along with the time zone offset.

SQL SYSDATETIMEOFFSET Syntax

The syntax of the SQL Server SYSDATETIMEOFFSET Statement is

SYSDATETIMEOFFSET()

SQL SYSDATETIMEOFFSET Function Example

The SQL Server SYSDATETIMEOFFSET function returns the format is: ‘yyyy-mm-dd hh:mm:ss.nnnnnnn’ (you can see that, the fractional seconds precision = 7) and datetime2 as a data type.

SELECT SYSDATETIMEOFFSET() AS [Current_Date];
SQL SYSDATETIMEOFFSET Function 1

SQL SYSDATETIMEOFFSET Function Example 2

This function returns the current system date and time of type datetime2. In this example, we are going to show you the SQL Server SYSUTCDATETIMEOFFSET function with examples.

SELECT 'Today' AS 'TODAY', SYSDATETIMEOFFSET() AS [Current_Date];

SELECT 'Milli Seconds' AS 'MILLI', DATEPART(millisecond, SYSDATETIMEOFFSET()) AS [Milli_Seconds]; 
SELECT 'Micro Seconds' AS 'MICRO', DATEPART(microsecond, SYSDATETIMEOFFSET()) AS [Micro_Seconds]; 
SELECT 'Nano Seconds' AS 'NANO', DATEPART(nanosecond, SYSDATETIMEOFFSET()) AS [Nano_Seconds];  
SELECT 'Year' AS 'YEAR', DATEPART(year, SYSDATETIMEOFFSET()) AS [Present_Year]; 

SELECT 'Month' AS 'MONTH', DATENAME(month, SYSDATETIMEOFFSET()) AS [Month_Name]; 
SELECT 'Tomorrow' AS 'DAY', DATEADD(day, 1, SYSDATETIMEOFFSET()) AS [Next_Day];
SQL SYSDATETIMEOFFSET Function 2

ANALYSIS

  • First four SQL Server statements, We used the DATEPART function to display the Year, Milliseconds, Microseconds, and Nanoseconds from today’s date & time
  • Next, We used the DATENAME function to display the weekday name from today’s date & time
  • Finally, We used the DATEADD function to display the Tomorrow’s date & time

SYSDATETIMEOFFSET Function Example 3

In this example, we are going to find the differences in Minutes, Seconds, and Milliseconds between the variable and Current Datetime. For this, we are using SQL DATEDIFF function.

DECLARE @datetime DATETIME2
SET @datetime = '2016-08-27 05:45:22.3021234'

SELECT 'Minutes_Difference' AS 'MINUTE', DATEDIFF(MINUTE, @datetime, SYSDATETIMEOFFSET()) AS [Number of Minutes];

SELECT 'Seconds_Difference' AS 'SECOND', DATEDIFF(SECOND, @datetime, SYSDATETIMEOFFSET()) AS [Number of Seconds];

SELECT 'Millisecond_Diff' AS 'MILLISECOND', DATEDIFF(MILLISECOND, @datetime, SYSDATETIMEOFFSET()) AS [Number of MilliSeconds];
SQL SYSDATETIMEOFFSET Function 3

From the above screenshot, you can observe that we used the DATEDIFF Date and Time Function. It finds the difference between the current date and time offset and the @datetime variable that we declared. I suggest you refer to the DATEDIFF function article to understand the above code.