Working with Dates in SQL Server

Date and time handling is critical in almost every database application. SQL Server provides a comprehensive set of date functions and data types. This tutorial covers the essential functions you need to query, calculate, and format dates in T-SQL.

Date Data Types

SQL Server offers several date/time types. DATE stores only the date, TIME stores only the time, DATETIME2 is the recommended type for date and time together (up to 100 nanosecond precision), and DATETIMEOFFSET includes timezone information.

CREATE TABLE Events (
    EventID INT IDENTITY PRIMARY KEY,
    EventName NVARCHAR(100),
    EventDate DATE,
    StartTime TIME(0),
    CreatedAt DATETIME2(3) DEFAULT SYSDATETIME(),
    ScheduledAt DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
);

Getting the Current Date and Time

SQL Server provides several functions to get the current date/time. GETDATE() returns DATETIME, while SYSDATETIME() returns the more precise DATETIME2.

SELECT
    GETDATE() AS CurrentDateTime,
    SYSDATETIME() AS PreciseDateTime,
    CAST(GETDATE() AS DATE) AS TodayDate,
    SYSDATETIMEOFFSET() AS WithTimezone;

DATEADD: Adding to Dates

DATEADD adds a specified interval to a date. The first argument is the date part (year, month, day, hour, minute, second), followed by the number to add and the date value.

SELECT
    DATEADD(DAY, 30, GETDATE()) AS In30Days,
    DATEADD(MONTH, -3, GETDATE()) AS ThreeMonthsAgo,
    DATEADD(YEAR, 1, '2024-06-15') AS NextYear;

-- Find orders expiring within 7 days
SELECT * FROM Subscriptions
WHERE ExpirationDate <= DATEADD(DAY, 7, GETDATE());

DATEDIFF: Calculating Differences

DATEDIFF returns the difference between two dates in a specified unit. Note that it counts boundaries crossed, not full intervals.

SELECT
    DATEDIFF(DAY, '2024-01-01', '2024-03-15') AS DaysBetween,   -- 74
    DATEDIFF(MONTH, '2024-01-15', '2024-03-10') AS MonthsDiff,  -- 2
    DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsEmployed
FROM Employees;

DATEPART, YEAR, MONTH, DAY

Extract specific components from a date. The shorthand functions YEAR(), MONTH(), and DAY() are convenient alternatives to DATEPART.

SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    DAY(OrderDate) AS OrderDay,
    DATEPART(WEEKDAY, OrderDate) AS DayOfWeek,
    DATENAME(MONTH, OrderDate) AS MonthName
FROM Orders;

FORMAT: Display-Friendly Dates

The FORMAT function (SQL Server 2012+) uses .NET format strings to produce human-readable date output. It is slower than CONVERT for large datasets, so use it mainly for presentation.

SELECT
    FORMAT(GETDATE(), 'yyyy-MM-dd') AS ISODate,
    FORMAT(GETDATE(), 'MMMM dd, yyyy') AS LongDate,
    FORMAT(GETDATE(), 'hh:mm tt') AS TimeAMPM,
    FORMAT(GETDATE(), 'dd/MM/yyyy', 'en-GB') AS UKFormat;

Date functions are among the most frequently used features in SQL Server. Master DATEADD and DATEDIFF for calculations, DATEPART for extraction, and FORMAT for display. Always prefer DATETIME2 over the legacy DATETIME type for new columns.