In this SQL Server tutorial, you will understand how to use the SYSDATETIME function in SQL Server.
You will learn the syntax of the SYSDATETIME() function and the difference between other date/time functions and the SYSDATETIME() function.
Then, you will understand how to use the SYSDATETIME() function with table columns to retrieve the information based on the date and time value.
SYSDATETIME Function in SQL Server
The SYSTEMDATETIME function in SQL Server retrieves the current datetime value of the system where the SQL Server instance runs. But other functions such as CURRENT_TIMESTAMP and GETDATE() do the same work as the SYSTEMDATETIME function.
The only difference is that the SYSDATETIME function in SQL Server returns the current date and time value with high precision.
Also, this function is Time Zone Awareness, which means it can return the timestamp value according to the time zone of the computer where the SQL Server instance is running.
The syntax is given below.
- SYSDATETIME(): It is a function that doesn’t take any arguments, and the return type of this function is datetime2 datatype, which contains both data and time with fractions of seconds.
The SYSDATETIME() returns a varying value for each call of this function. That’s why it is called a non-deterministic function because its output changes as many times as it is executed.
- As you know, SYSDATETIME() is a non-deterministic function, so it doesn’t allow the indexing of views or expressions that contain this function.
Let’s see an example of how to get the current system time using the SYSDATETIME function in SQL Server.
So for that, use the below query.
SELECT SYSDATETIME() AS SystemDateAndTime;
As you can see in the above output, the SYSDATETIME() function returns the current system time which is 2023-11-21 15:17:30.8251472, but here fraction part is in 7 digits (8251472).
But in the case of CURRENT_TIMESTAMP, GETDATE(), the fractional part is only 3 digits. The reason behind the return type of the functions where SYSDATETIME() returns the DATETIME2 data type.
- The DATETIME2 is a data type that stores date and time information, and it is an extension of the DATETIME data type. DATETIME2 allows you to define the fractional seconds precision from zero to seven digits, with a default of 7 digits.
- Remember, DATETIME2 stores time with a precision of up to 100 nanoseconds.
The SYSDATETIME() function returns the datetime value in the format ‘YYYY-MM-DD hh:mm:ss. nnnnnnn’, which is the standard SQL format for datetime data types.
- YYYY: It represents the four-digit year, for example, 2023.
- MM: It represents the two-digit month, for example, 11.
- DD: It represents the two-digit day, for example, 21.
- hh: It represents the two-digit hour in 24-hour format, for example, 15 hours.
- mm: It represents the two-digit minutes, for example, 17 minutes.
- ss: It represents the two-digit seconds, for example, 30 seconds.
- nnnnnnn: It represents the fractional seconds (milliseconds), which is seven decimal places long by default, for example, 8251472 fractional seconds.
Now, you know how to get the current date and time of the system using the SYSDATETIME() function. This is how you can use the SYSDATETIME function in SQL Server to get the current timestamp value with high precision.
Let’s see how to use the SYSDATETIME() function with columns of the table in the next section.
SYSDATETIME Function in SQL Server with column of the Table
You can use the SYSDATETIME() function with a table column for a different purpose that depends upon your requirements such as for comparison, or analyzing time-based data.
Here you will learn how to compare the timestamp value using the SYSDATETIME function in SQL Server to retrieve the required information from the table.
For example, consider you have an Orders table with columns OrderID, CustomerName, OrderDate, and OrderStatus which is shown below.
Now you have a task where you to find all the orders with an order date and time greater than the current date and time, and the order status is Processing. For that use the below query.
SELECT * FROM Orders
WHERE OrderDate > SYSDATETIME()
AND OrderStatus = 'Processing';
As you can see from the above output, there are three orders in processing mode, and the order date and time are greater than the current date and time. For example, Henry Clark order is in processing mode, and the order date is 2023-11-25 at 13:30:00.0000000.
If you want to use the SYSDATETIME function in SQL Server with other functions in SQL Server, such as DATEDIFF(), then you can use it.
For example, if you want to calculate the number of days since each order was delivered, then you can use the below query for that.
SELECT OrderID, ABS(DATEDIFF(DAY, OrderDate, SYSDATETIME())) AS DaysAgo
WHERE OrderStatus = 'Delivered';
When you execute the above query, it returns a result set containing the order ID and the number of days since each order was delivered; for example, the order with an ID equal to 7 was delivered 4 days ago from the current date and time.
Let’s understand this query part ABS(DATEDIFF(DAY, OrderDate, SYSDATETIME())), the DATEDIFF() function computes the difference in days between OrderDate and the current system date/time (SYSDATETIME()), and the ABS() function converts any negative result to positive.
The result is converted to a positive number using the ABS() to get the number of days as a positive integer regardless of whether the OrderDate is in the past or future relative to the current date.
You can use the SYSDATETIME function in SQL Server in many ways while manipulating the date and time data or information.
In this SQL Server tutorial, you learned how to return the current system time using the SYSDATETIME function in SQL Server. Also, you have used the SYSDATETIME() function with the table column for comparison purposes.
You may like to read:
- SYSDATETIMEOFFSET Function in SQL Server
- How to use GETDATE Function in SQL Server
- How to Use CURRENT_TIMESTAMP Function in SQL Server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.