The SYSDATETIME() function returns the current date and time as a datetime2(7) value. This value is derived from the operating system of the computer that the instance of SQL Server is running on.

This article provides examples of the SYSDATETIME() function, including how you can use it with other functions to return the value you’re interested in.

Syntax

First, here’s the syntax:

SYSDATETIME ( )

So this function doesn’t actually accept any arguments. You simply call it without any arguments.

Example

Here’s a basic example of using a SELECT statement to return the current date and time from SYSDATETIME():

SELECT SYSDATETIME() AS Result;

Result:

+-----------------------------+
| Result                      |
|-----------------------------|
| 2018-06-15 23:09:13.5852199 |
+-----------------------------+

So as mentioned, it returns a datetime2(7) value. The datetime2 data type has a larger date range and a larger default fractional precision than the datetime data type (which is the data type that GETDATE() returns – see SYSDATETIME() vs GETDATE(): What’s the Difference?).

Extract a Part of the Date

If you only want a part of the return value, you can use DATEPART() to return only that part of the date/time that you’re interested in.

Example:

SELECT DATEPART(month, SYSDATETIME()) AS Result;

Result:

+----------+
| Result   |
|----------|
| 6        |
+----------+

Sometimes there’s more than one way to get the same result in SQL Server. Here’s another example using the MONTH() function:

SELECT MONTH(SYSDATETIME()) AS Result;

Result:

+----------+
| Result   |
|----------|
| 6        |
+----------+

Both of those functions returned the current month. But they returned them as an integer representing the month number.

If you want the month name returned instead, you can use DATENAME():

SELECT DATENAME(month, SYSDATETIME()) AS Result;

Result:

+----------+
| Result   |
|----------|
| June     |
+----------+

 

Format the Date

You can also use other T-SQL functions to format the date as required.

Here’s an example of using the FORMAT() function to format the result:

SELECT 
    FORMAT(SYSDATETIME(), 'd', 'en-US') AS 'd, en-US',
    FORMAT(SYSDATETIME(), 'd', 'en-gb') AS 'd, en-gb',
    FORMAT(SYSDATETIME(), 'D', 'en-US') AS 'D, en-US',
    FORMAT(SYSDATETIME(), 'D', 'en-gb') AS 'D, en-gb';

Result:

+------------+------------+-----------------------+--------------+
| d, en-US   | d, en-gb   | D, en-US              | D, en-gb     |
|------------+------------+-----------------------+--------------|
| 6/15/2018  | 15/06/2018 | Friday, June 15, 2018 | 15 June 2018 |
+------------+------------+-----------------------+--------------+

More examples at How to Format the Date and Time in SQL Server.

Incrementing the Value and Finding the Difference

You can use functions like DATEDIFF() to return the difference between the current date and another date.

Here’s an example of using DATEADD() to add a month to the current date, then finding out the difference in days:

DECLARE @date1 datetime2 = SYSDATETIME();
DECLARE @date2 datetime2 = DATEADD(month, 1, SYSDATETIME());
SELECT DATEDIFF(day, @date1, @date2) AS Result;

Result:

+----------+
| Result   |
|----------|
| 30       |
+----------+