SQL SYSDATETIMEOFFSET

The SQL Server 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. The syntax of the SYSDATETIMEOFFSET Statement is

SYSDATETIMEOFFSET()

SQL SYSDATETIMEOFFSET Example

The 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];
Print Current date 1

SYSDATETIMEOFFSET Example 2

This function returns the current system date and time of type datetime2. In this example, we are going to show you the SYSDATETIMEOFFSET function with examples. Please refer to the list of SQL Date functions available on our SQL Tutorial page.

  • The 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 Tomorrow’s date & time.
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

SQL Server SYSDATETIMEOFFSET Example 3

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

TIP: Please refer to the SQL GETDATE and SQL SYSDATETIME functions.

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];
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.

Categories SQL