Date and Time Functions in SQL Server T-SQL

Like other database management systems, SQL Server has its own date and time functions to facilitate data and time operation in the SQL scripts.

SQL Server date and time data types

Before moving to functions, we’ll need to mention the date and time data types these functions work with. Here is a list of all SQL Server date and time data types (starting with the most commonly used):

• date – format is YYYY-MM-DD; stores values from 0001-01-01 to 9999-12-31; with the accuracy of 1 day (there is no approximation here because acts same as integer values); uses 3 bytes
• datetime –format is YYYY-MM-DD hh:mm:ss[.nnn]; stores values from 1753-01-01 to 9999-12-31; with the accuracy of 0.00333 seconds (please notice we have approximation here); uses 8 bytes
• time – format is hh:mm:ss[.nnnnnnn]; stores values from 00:00:00.0000000 to 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 3 to 5 bytes
• smalldatetime – format is YYYY-MM-DD hh:mm:ss; stores values from 1900-01-01 to 2079-06-06; with the accuracy of 1 minute; uses 4 bytes
• datetime2 –format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 6 to 8 bytes
• datetimeoffset – format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 8 to 10 bytes

In most cases, you’ll use either datetime, either date. The remaining 4 types are here if you want to have higher accuracy (datetime2, datetimeoffset), lower accuracy (smalldatetime), or store only time (time).

Higher-Precision System Date and Time Functions

SQL Server High Precision Date and Time Functions have a scale of 7 and are:

SYSDATETIME – returns the date and time of the machine the SQL Server is running on

SYSDATETIMEOFFSET – returns the date and time of the machine the SQL Server is running on plus the offset from UTC

SYSUTCDATETIME - returns the date and time of the machine the SQL Server is running on as UTC

-- higher precision functions
SELECT SYSDATETIME()       AS 'DateAndTime';        -- return datetime2(7)      
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7)
SELECT SYSUTCDATETIME()    AS 'DateAndTimeInUtc';   -- returns datetime2(7)

Function to Validate Date and Time Values

ISDATE – returns int - Returns 1 if a valid datetime type and 0 if not

-- validate date and time - returns int
SELECT ISDATE(GETDATE()) AS 'IsDate';
SELECT ISDATE(NULL) AS 'IsDate';