Tutorial Gateway

SQL SYSDATETIME Function

by

SQL SYSDATETIME function is a SQL Date Function, which is used to return the Current Date and Time of the computer on which the SQL Server instance is running. The syntax of the SQL Server SYSDATETIME Statement is

SYSDATETIME()

NOTE: This function is similar to SQL GETDATE () function and the differences are:

  • SYSDATETIME returns the fractional seconds precision upto 7, whereas the GETDATE return upto 3
  • SYSDATETIME returns datetime2 as the data type, whereas the GETDATE return DateTime

SQL SYSDATETIME Function Example 1

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

SELECT SYSDATETIME() AS [Current_Date]

OUTPUT

SQL SYSDATETIME Function 1

SQL SYSDATETIME Function Example 2

In this example, we are going to show you the SQL Server SYSDATETIME examples.

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

SELECT 'Milli Seconds' AS 'MILLI', DATEPART(millisecond, SYSDATETIME()) AS [Milli_Seconds]; 
SELECT 'Micro Seconds' AS 'MICRO', DATEPART(microsecond, SYSDATETIME()) AS [Micro_Seconds]; 
SELECT 'Nano Seconds' AS 'NANO', DATEPART(nanosecond, SYSDATETIME()) AS [Nano_Seconds]; 

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

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

OUTPUT

SQL SYSDATETIME Function 2

ANALYSIS

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

SELECT 'Milli Seconds' AS 'MILLI', DATEPART(millisecond, SYSDATETIME()) AS [Milli_Seconds]; 
SELECT 'Micro Seconds' AS 'MICRO', DATEPART(microsecond, SYSDATETIME()) AS [Micro_Seconds]; 
SELECT 'Nano Seconds' AS 'NANO', DATEPART(nanosecond, SYSDATETIME()) AS [Nano_Seconds];

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

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

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

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

SYSDATETIME Function Example 3

In this example, we use the SQL DATEDIFF function to check for Employee details such as – What year we hired him, how many weeks he associated with our company, etc.

SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
      ,[Occupation]
      ,[YearlyIncome]
      ,[HireDate]
      ,DATEDIFF (year, [HireDate], SYSDATETIME()) AS [YEARS]
      ,DATEDIFF (day, [HireDate], SYSDATETIME()) AS [DAYS]
      ,DATEDIFF (WEEK, [HireDate], SYSDATETIME()) AS [WEEKS]
      ,DATEDIFF (WEEKDAY, [HireDate], SYSDATETIME()) AS [WEEKDAY]
  FROM [SQL Tutorial].[dbo].[Employes]

OUTPUT

SQL SYSDATETIME Function 3

From the above screenshot you can observe that, we are finding the difference between the Employee Hire date and Today’s system date & time (SYSDATETIME()) using the DATEDIFF function.

Placed Under: SQL