SQL Server SYSDATETIME Function

By: Daniel Calbimonte

The SYSDATETIME() function returns the server date time stamp using the datetime2 format. This function has a better precision than the GETDATE() or GETUTCDATE(). The data type returned by this function is datetime2(7).

Syntax

SYSDATETIME()

Parameters

• No parameters required

Simple SYSDATETIME Example

The following example will show the output for SYSDATETIME.

SELECT SYSDATETIME() as date

As you can see, there are 7 positions for nanoseconds (8324291).

Difference between GETDATE and SYSDATETIME

The following example shows the difference in nanoseconds between the GETDATE function and SYSDATETIME.

SELECT DATEDIFF(NANOSECOND, GETDATE(), SYSDATETIME()) as difference

As you can see, the SYSDATETIME function is more precise.

SELECT DATEDIFF(NANOSECOND,GETDATE(),SYSDATETIME()) as difference

GETDATE has 3 positions for nanoseconds and SYSDATETIME has 7 positions.

How to set the SYSDATETIME in a custom format

The following example shows the SYSDATETIME in the dd-MM-yyyy hh:mm:ss tt format.

SELECT FORMAT(SYSDATETIME(),'dd-MM-yy hh:mm:ss tt') as formatdate

How to set the SYSDATETIME in a different cultural format

The following example will set the SYSDATETIME in Arabic date format.

SELECT FORMAT(SYSDATETIME(), 'dddd dd, MMMM, yyyy','AR-ar') as arabic

Use SYSDATETIME with Data from a Table

The following example will show the products with a StartDate between 8 and 9 years ago.

SELECT Productid, DATEDIFF(YEAR, StartDate, SYSDATETIME()) as years
FROM Production.ProductCostHistory
WHERE DATEDIFF(YEAR, StartDate, SYSDATETIME()) BETWEEN 8 AND 9

Related Articles

• SQL CURRENT_TIMESTAMP
• SQL GETDATE
• SQL GETUTCDATE
• SQL DATEDIFF
• SQL YEAR
• Mimic timestamp behavior of other database platforms to store last modified date
• SQL Convert Date to YYYYMMDD
• SQL Server DIFFERENCE Function