SQL Server SYSDATETIME Function


By:

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

SYSDATETIME function example

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
SYSDATETIME function difference with GETDATE

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.

SYSDATETIME function example and getdate

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
SYSDATETIME function custom format example

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
SYSDATETIME function cultural date example

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
SYSDATETIME function example with tables

Related Articles


Last Update: 1/5/2022




Comments For This Article





download














get free sql tips
agree to terms