Microsoft Docs

Date and Time Data Types and Functions (Transact-SQL)

This document details the date and time data types and related functions available in Transact-SQL for SQL Server. Understanding these is crucial for managing temporal data effectively within your databases.

Overview of Date and Time Data Types

SQL Server offers several data types for storing date and time information, each with varying precision and storage requirements. The choice of data type depends on the specific needs of your application regarding accuracy and the range of dates and times to be stored.

1. DATE

Stores a date. The range is from '0001-01-01' through '9999-12-31'.

-- Example declaration
            DECLARE @myDate DATE = '2023-10-27';
            SELECT @myDate;

2. TIME

Stores a time of day. The range is from 00:00:00.0000000 through 23:59:59.9999999.

-- Example declaration
            DECLARE @myTime TIME(4) = '14:30:05.1234';
            SELECT @myTime;

3. DATETIME

Stores a date and time combination. The range is from January 1, 1753, through December 31, 9999. It has a precision of milliseconds.

-- Example declaration
            DECLARE @myDateTime DATETIME = '2023-10-27 14:30:05.123';
            SELECT @myDateTime;

4. SMALLDATETIME

Stores a date and time combination. The range is from January 1, 1900, through June 6, 2079. It has a precision of minutes.

-- Example declaration
            DECLARE @mySmallDateTime SMALLDATETIME = '2023-10-27 14:30';
            SELECT @mySmallDateTime;

5. DATETIME2

An extension of the older DATETIME data type. It supports a wider range of dates and user-configurable precision. The range is from January 1, 0001, through December 31, 9999.

-- Example declaration with precision 7
            DECLARE @myDateTime2 DATETIME2(7) = '2023-10-27 14:30:05.1234567';
            SELECT @myDateTime2;

6. DATETIMEOFFSET

Stores a date and time combination, plus a time zone offset. This is useful for applications that need to store events across different time zones.

-- Example declaration
            DECLARE @myDateTimeOffset DATETIMEOFFSET = '2023-10-27 14:30:05.1234567 +05:00';
            SELECT @myDateTimeOffset;

Common Date and Time Functions

Transact-SQL provides a rich set of functions for manipulating and querying date and time data.

Getting the Current Date and Time

• GETDATE(): Returns the current database system date and time as a DATETIME value.
• SYSDATETIME(): Returns the current database system date and time as a DATETIME2(7) value. This is the preferred function for high precision.
• GETUTCDATE(): Returns the current UTC date and time as a DATETIME value.

SELECT GETDATE() AS CurrentDateTime,
                   SYSDATETIME() AS CurrentDateTime2,
                   GETUTCDATE() AS CurrentUTCDateTime;

Extracting Date/Time Parts

• DATEPART(datepart, date): Returns an integer representing the specified part of a date.
• DATENAME(datepart, date): Returns a character string representing the specified part of a date.

DECLARE @sampleDate DATETIME = '2023-10-27 14:30:05';
            SELECT
                DATEPART(year, @sampleDate) AS YearPart,
                DATENAME(weekday, @sampleDate) AS DayOfWeek;

Date and Time Arithmetic

• DATEADD(datepart, number, date): Returns a new datetime value to which is added a specified time interval.
• DATEDIFF(datepart, startdate, enddate): Returns the difference between two specified dates, in the specified datepart of that difference.

DECLARE @startDate DATETIME = '2023-10-01';
            DECLARE @endDate DATETIME = '2023-10-27';

            SELECT
                DATEADD(day, 7, @startDate) AS SevenDaysLater,
                DATEDIFF(day, @startDate, @endDate) AS DaysDifference;

Formatting Dates

While FORMAT() (SQL Server 2012+) offers extensive formatting, CONVERT() is widely used for simpler conversions.

• CONVERT(datatype, expression, style): Converts an expression from one data type to another. The style parameter is crucial for date formatting.

DECLARE @myDate DATE = '2023-10-27';
            -- Style 101: mm/dd/yyyy
            -- Style 103: dd/mm/yyyy
            -- Style 112: yyyymmdd (ISO)
            SELECT
                CONVERT(VARCHAR, @myDate, 101) AS USFormat,
                CONVERT(VARCHAR, @myDate, 103) AS EuropeanFormat,
                CONVERT(VARCHAR, @myDate, 112) AS ISOFormat;

Best Practices

• Use DATETIME2 for new applications to benefit from its wider range and precision.
• Be mindful of time zone differences when storing and retrieving date/time data. Use DATETIMEOFFSET when necessary.
• Use SYSDATETIME() instead of GETDATE() for higher precision.
• Understand the style codes for CONVERT() to ensure correct date formatting.