web analytics

Date and Time Functions in SQL Server T-SQL

Options

codeling 1524 - 6369
@2021-04-02 14:31:07

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 (datetime2datetimeoffset), lower accuracy (smalldatetime), or store only time (time).

@2021-04-02 14:37:32

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)

SQL Server T-SQL Code Date Function Result
SELECT SYSDATETIME() AS 'DateAndTime'; -- return datetime2(7) DateAndTime 2021-03-02 15:07:15.5331417
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7) DateAndTime+Offset 2021-03-02 15:07:15.5331417 -04:00
SELECT SYSUTCDATETIME() AS 'DateAndTimeInUtc'; -- returns datetime2(7) DateAndTimeInUtc 2021-03-02 19:07:15.5331417
@2021-04-02 15:04:13

Lower-Precision System Date and Time Functions

SQL Server Lesser Precision Data and Time Functions have a scale of 3 and are:

CURRENT_TIMESTAMP - returns the date and time of the machine the SQL Server is running on

GETDATE() - returns the date and time of the machine the SQL Server is running on

GETUTCDATE() - returns the date and time of the machine the SQL Server is running on as UTC

-- lesser precision functions - returns datetime
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parentheses  
SELECT GETDATE()         AS 'DateAndTime';   
SELECT GETUTCDATE()      AS 'DateAndTimeUtc'; 

SQL Server T-SQL Code Date Function Result
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parentheses DateAndTime 2021-03-02 15:09:19.023
SELECT GETDATE() AS 'DateAndTime'; DateAndTime 2021-03-02 15:09:19.023
SELECT GETUTCDATE() AS 'DateAndTimeUtc'; DateAndTimeUtc 2021-03-02 19:09:19.023
@2021-04-02 15:05:02

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';

SQL Server T-SQL Code Date Function Result
SELECT ISDATE(GETDATE()) AS 'IsDate'; IsDate 1
SELECT ISDATE(NULL) AS 'IsDate'; IsDate 0

Comments

You must Sign In to comment on this topic.


© 2021 Digcode.com