SQL SYSDATETIMEOFFSET Function

The SQL SYSDATETIMEOFFSET function is a SQL Date Function, which returns the datetimeoffset (7) of Current Date and Time of the computer on which the SQL 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 1

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 SQL Server 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 SQL DATEPART function to display the Year, Milliseconds, Microseconds, and Nanoseconds from today’s date & time
  • Next, We used the SQL DATENAME function to display the weekday name from today’s date & time
  • Finally, We used the SQL DATEADD function to display the Tomorrow 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 SQL DATEDIFF function article to understand the above code.