SQL Server Date Functions

T-SQL's date toolbox is large and shows its age in places: two current-time functions that differ in precision, a legacy datetime type you should stop using, a DATEDIFF that counts boundaries rather than elapsed time, and a FORMAT function that is convenient and slow. This guide covers the functions that matter and the traps in each.

Pick the Right Type First

Use datetime2 for new work. The legacy datetime type rounds to increments of .000, .003, and .007 seconds -- insert '2026-07-09 12:00:00.001' and read back .000. That rounding breaks equality comparisons against values generated elsewhere. datetime2(3) gives you real millisecond precision in 7 bytes, less than datetime costs. Microsoft's own docs say to avoid datetime for new development.

Store UTC unless you have a strong reason not to, and use datetimeoffset when the offset itself is data (multi-region event logs).

Current Date and Time

SELECT
    GETDATE()            AS dt_legacy,      -- datetime, ~3.33 ms
    SYSDATETIME()        AS dt2,            -- datetime2(7), 100 ns
    GETUTCDATE()         AS utc_legacy,     -- datetime, UTC
    SYSUTCDATETIME()     AS utc_dt2,        -- datetime2(7), UTC
    SYSDATETIMEOFFSET()  AS dto;            -- datetimeoffset, server TZ

GETDATE() survives in every codebase; prefer the SYS* family, which returns datetime2 at full precision. For "today":

SELECT CAST(SYSDATETIME() AS date);

DATEADD and DATEDIFF

SELECT DATEADD(day, 30, '2026-07-09');       -- 2026-08-08
SELECT DATEADD(month, 1, '2026-01-31');      -- 2026-02-28 (clamps to month end)
SELECT DATEDIFF(day, '2026-01-01', '2026-07-09');  -- 189

The Boundary-Counting Trap

DATEDIFF counts datepart boundaries crossed, not elapsed time:

SELECT DATEDIFF(year, '2025-12-31 23:59:59', '2026-01-01 00:00:01');  -- 1 "year"
SELECT DATEDIFF(hour, '2026-07-09 08:59:00', '2026-07-09 09:01:00');  -- 1 "hour"

Two seconds apart, one year difference. If you need elapsed time, diff at a finer grain and divide:

SELECT DATEDIFF(second, start_at, end_at) / 3600.0 AS hours_elapsed;

DATEDIFF returns int, which overflows at ~68 years of seconds and ~25 days of milliseconds. DATEDIFF_BIG (SQL Server 2016+) returns bigint and fixes that.

Truncating Dates: DATETRUNC and DATE_BUCKET (2022)

Before SQL Server 2022, truncating to month meant DATEADD/DATEDIFF gymnastics or string casts. Now:

SELECT DATETRUNC(month, SYSDATETIME());   -- 2026-07-01 00:00:00.0000000
SELECT DATETRUNC(week, SYSDATETIME());    -- respects DATEFIRST setting

Monthly aggregation:

SELECT DATETRUNC(month, order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY DATETRUNC(month, order_date)
ORDER BY month;

DATE_BUCKET (also 2022) generalizes truncation to arbitrary interval widths -- 15-minute buckets, 10-day buckets -- counted from an origin (default 1900-01-01):

SELECT DATE_BUCKET(minute, 15, event_at) AS bucket, COUNT(*) AS events
FROM telemetry
GROUP BY DATE_BUCKET(minute, 15, event_at);

On older versions the idiom is:

-- truncate to month, pre-2022
SELECT DATEADD(month, DATEDIFF(month, 0, order_date), 0);

DATEFROMPARTS, DATETIME2FROMPARTS (2012+) build dates from components; EOMONTH(date [, months_offset]) gives the last day of a month -- handy for billing periods.

Extracting Parts

SELECT
    YEAR(order_date), MONTH(order_date), DAY(order_date),
    DATEPART(quarter, order_date),
    DATEPART(iso_week, order_date),   -- ISO 8601 week, use this one
    DATENAME(weekday, order_date);    -- 'Thursday' (language-dependent!)

Two traps: DATEPART(week, ...) depends on DATEFIRST and does not follow ISO 8601 -- use iso_week for anything week-numbered. DATENAME output changes with SET LANGUAGE, so never parse or compare it.

Formatting: CONVERT Styles vs FORMAT

SELECT CONVERT(varchar(10), SYSDATETIME(), 23);   -- 2026-07-09 (ISO)
SELECT CONVERT(varchar(8),  SYSDATETIME(), 112);  -- 20260709
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm'); -- .NET format strings

FORMAT is far more readable and far slower -- it calls into the CLR per row and commonly benchmarks 10-40x slower than CONVERT. Fine for a report footer; wrong for a million-row export. Formatting belongs in the application layer anyway; keep dates typed as long as possible.

Parsing Strings Safely

String-to-date interpretation of '07/09/2026' depends on the session's DATEFORMAT/language -- July 9 or September 7. Always pass unambiguous ISO forms, and know that for datetime2 the safe literal is '2026-07-09T14:30:00' or '20260709'. Then:

SELECT TRY_CONVERT(datetime2, '2026-07-09T14:30:00');  -- NULL on failure, no error
SELECT TRY_CONVERT(datetime2, 'garbage');              -- NULL

Prefer TRY_CONVERT/TRY_CAST over ISDATE -- ISDATE answers for the legacy datetime range only (nothing before 1753) and is session-language sensitive.

Time Zones: AT TIME ZONE (2016+)

SQL Server stores no time zone in datetime2. AT TIME ZONE converts using Windows time zone names, DST-aware:

-- interpret a naive datetime2 as UTC, then convert
SELECT CONVERT(datetime2, '2026-07-09 12:00:00')
       AT TIME ZONE 'UTC'
       AT TIME ZONE 'Central European Standard Time';  -- 14:00 +02:00 (DST-aware)

The first AT TIME ZONE on a naive value labels it (produces a datetimeoffset); the second converts. Windows names ('Central European Standard Time'), not IANA ('Europe/Amsterdam') -- list them with SELECT name FROM sys.time_zone_info. Despite the word "Standard" in the name, conversions apply DST correctly.

Sargable Date Filters

Wrapping the column in a function kills index seeks:

-- BAD: scans, function on the column
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 7
 
-- GOOD: seeks, half-open range
WHERE order_date >= '2026-07-01' AND order_date < '2026-08-01'

The half-open upper bound (< first day of next period) also sidesteps the "23:59:59.997 vs .9999999" end-of-day precision mess entirely. One notable exception: CAST(dt_col AS date) = '2026-07-09' is sargable -- the optimizer rewrites it into a range -- but the explicit range is clearer and works everywhere.

Common Mistakes

• Using datetime for new columns -- 3.33 ms rounding breaks comparisons; use datetime2.
• Trusting DATEDIFF as elapsed time -- it counts boundaries; diff in seconds and divide.
• BETWEEN for date ranges -- BETWEEN '2026-07-01' AND '2026-07-31' misses July 31 rows with a time component. Half-open ranges.
• FORMAT in hot paths -- 10-40x slower than CONVERT.
• Ambiguous literals -- '07/09/2026' flips with language settings; use ISO 8601.
• DATEPART(week, ...) for ISO weeks -- use iso_week.

Mako's AI autocomplete is useful here -- describe "monthly totals for the last 12 months" and it drafts the DATETRUNC + half-open-range query for the dialect you're connected to.

Related Guides

• Window functions in SQL Server
• CTEs in SQL Server
• Indexes in SQL Server explained
• Date functions in PostgreSQL
• Date functions in MySQL

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.