SQL SYSDATETIME Function Use and Examples

Overview

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).

Explanation

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

Additional Information

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

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018