SQL SYSDATETIME function is one of the Date Function, which is used to return the Current Date and Time of the computer on which the SQL Server instance is running.
NOTE: This function is similar to SQL GETDATE () function and the differences are:
- SYSDATETIME returns the fractional seconds precision upto 7, where as the GETDATE return upto 3
- SYSDATETIME returns datetime2 as the data type, where as the GETDATE return datetime
SQL SYSDATETIME Syntax
The basic syntax of the SYSDATETIME Statement in SQL Server is as follows:
|
1 |
SYSDATETIME() |
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).
SQL CODE
|
1 |
SELECT SYSDATETIME() AS [Current_Date] |
OUTPUT
SQL SYSDATETIME Function Example 2
In this example, we are going show you the SQL Server SYSDATETIME examples.
SQL CODE
|
1 2 3 4 5 6 7 8 9 |
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
ANALYSIS
We used the SQL DATEPART function to display the Milliseconds, Microseconds, and Nanoseconds from today’s date & time
|
1 2 3 |
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
|
1 |
SELECT 'Day' AS 'DAY', DATENAME(WEEKDAY, SYSDATETIME()) AS [Day_Name]; |
We used the SQL DATEADD function to display the Tomorrow date & time
|
1 |
SELECT 'Tomorrow' AS 'DAY', DATEADD(day, 1, SYSDATETIME()) AS [Next_Date]; |
SQL SYSDATETIME Function Example 3
In this example, we are going to use SQL DATEDIFF function to check for Employee details such as: What year we hired him, how many weeks he associated with our company etc.
SQL CODE
|
1 2 3 4 5 6 7 8 9 |
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
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. I suggest you to refer SQL DATEDIFF function article to understand the above code.
Thank You for Visiting Our Blog
Leave a Reply