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:
|
1 |
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
|
1 |
SELECT SYSUTCDATETIME() AS [Current_Date] |
OUTPUT
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
|
1 2 3 4 5 6 7 8 9 |
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
ANALYSIS
We used the SQL DATEPART function to display the Microsecond, Milliseconds, and Nanoseconds from today’s date & time
|
1 2 3 |
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
|
1 |
SELECT 'Day' AS 'DAY', DATENAME(WEEKDAY, SYSUTCDATETIME()) AS [Day_Name]; |
We used the SQL DATEADD function to display the Tomorrow date & time
|
1 |
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
|
1 2 3 4 5 6 7 8 |
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
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
Leave a Reply