Tutorial Gateway

SQL SYSUTCDATETIME Function

by

The SQL SYSUTCDATETIME function is one of the Date Function, which is used to return the Current system time stamp as a datetime2 value. The return value of this function is the current Coordinated Universal Time (UTC time) which is derived from the operating System (OS) of the computer on which the SQL Server instance is running.

NOTE: This function is similar to SQL GETUTCDATE function and the differences are:

  • SYSUTCDATETIME returns the fractional seconds precision up to 7, where as the GETUTCDATE return up to 3
  • The SYSUTCDATETIME returns datetime2 as the data type, where as the GETUTCDATE return datetime

SQL SYSUTCDATETIME Syntax

The basic syntax of the SQL Server SYSUTCDATETIME is as shown below:

SYSUTCDATETIME()

SQL SYSUTCDATETIME Function Example 1

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

SQL CODE

SELECT SYSUTCDATETIME() AS [Current_Date]

OUTPUT

SQL SYSUTCDATETIME Function 1

SQL SYSUTCDATETIME Function Example 2

This function returns the Current system time stamp of type datetime2 and in this example, we are going show you the SQL Server SYSUTCDATETIME function with examples.

SQL CODE

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

SELECT 'Milli Seconds' AS 'MILLISECOND', DATEPART(millisecond, SYSUTCDATETIME()) AS [MilliSeconds_Value]; 
SELECT 'Micro Seconds' AS 'MICROSECOND', DATEPART(microsecond, SYSUTCDATETIME()) AS [MicroSeconds_Value]; 
SELECT 'Nano Seconds' AS 'NANOSECOND', DATEPART(nanosecond, SYSUTCDATETIME()) AS [NanoSeconds_Value]; 

SELECT 'Day' AS 'DAY', DATENAME(WEEKDAY, SYSUTCDATETIME()) AS [Day_Name]; 

SELECT 'Tomorrow' AS 'DAY', DATEADD(day, 1, SYSUTCDATETIME()) AS [Next_Date];

OUTPUT

SQL SYSUTCDATETIME Function 2

ANALYSIS

We used the SQL DATEPART function to display the Microsecond, Milliseconds, and Nanoseconds from today’s date & time

SELECT 'Milli Seconds' AS 'MILLISECOND', DATEPART(millisecond, SYSUTCDATETIME()) AS [MilliSeconds_Value]; 
SELECT 'Micro Seconds' AS 'MICROSECOND', DATEPART(microsecond, SYSUTCDATETIME()) AS [MicroSeconds_Value]; 
SELECT 'Nano Seconds' AS 'NANOSECOND', DATEPART(nanosecond, SYSUTCDATETIME()) AS [NanoSeconds_Value];

Next, We used the SQL DATENAME function to display the weekday name from today’s date & time

SELECT 'Day' AS 'DAY', DATENAME(WEEKDAY, SYSUTCDATETIME()) AS [Day_Name];

We used the SQL DATEADD function to display the Tomorrow date & time

SELECT 'Tomorrow' AS 'DAY', DATEADD(day, 1, SYSUTCDATETIME()) AS [Next_Date];

SQL SYSUTCDATETIME Function Example 3

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

SQL CODE

DECLARE @dtime DATETIME2
SET @dtime = '2016-08-27 03:54:27.5301234'

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

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

SELECT 'Millisecond_Diff' AS 'MILLISECOND', DATEDIFF(MILLISECOND, @dtime, SYSUTCDATETIME()) AS [Number of MilliSeconds];

OUTPUT

SQL SYSUTCDATETIME Function 3

From the above screenshot you can observe that, we used the DATEDIFF function to find the difference between the current date and time and @dtime variable that we declared. I suggest you to refer SQL DATEDIFF function article to understand the above code.

Thank You for Visiting Our Blog

Placed Under: SQL