08 - Date and Time Functions

38 min read

Date and Time Functions

Date and Time Data Types

Storage, precision, and range

The cost and reach of each temporal type.

Storage bytes per type

This query measures the on-disk footprint of representative temporal types.

Measure each type’s on-disk footprint with DATALENGTH().

DECLARE @d date              = '2025-04-08';
DECLARE @t time(7)           = '14:30:00.1234567';
DECLARE @dt2 datetime2(7)    = '2025-04-08 14:30:00.1234567';
DECLARE @dto datetimeoffset(7) = '2025-04-08 14:30:00.1234567 +02:00';
SELECT
    DATALENGTH(@d)   AS date_bytes,
    DATALENGTH(@t)   AS time7_bytes,
    DATALENGTH(@dt2) AS datetime2_7_bytes,
    DATALENGTH(@dto) AS dto_7_bytes;
date_bytestime7_bytesdatetime2_7_bytesdto_7_bytes
35810

Full range of date and datetime2

This query compares the minimum and maximum representable values.

Both types share a calendar range from year 1 to year 9999.

SELECT
    CAST('0001-01-01' AS date)                       AS date_min,
    CAST('9999-12-31' AS date)                       AS date_max,
    CAST('0001-01-01T00:00:00' AS datetime2)         AS dt2_min,
    CAST('9999-12-31T23:59:59.9999999' AS datetime2) AS dt2_max;
date_mindate_maxdt2_mindt2_max
0001-01-019999-12-310001-01-01 00:00:009999-12-31 23:59:59.999999

The dt2_max output shows .999999 not .9999999 because the pyodbc client truncates fractional seconds to microseconds when presenting values — the stored value is still datetime2(7).

Precision scale on datetime2

The (n) in datetime2(n) chooses how many fractional-second digits are stored.

Scale 0, 3, and 7

This query shows how datetime2 precision changes as scale drops.

Watch the tail of the fractional seconds get trimmed as the scale drops.

SELECT
    CAST('2025-04-08 14:30:00.1234567' AS datetime2(0)) AS dt2_0,
    CAST('2025-04-08 14:30:00.1234567' AS datetime2(3)) AS dt2_3,
    CAST('2025-04-08 14:30:00.1234567' AS datetime2(7)) AS dt2_7;
dt2_0dt2_3dt2_7
2025-04-08 14:30:002025-04-08 14:30:00.1232025-04-08 14:30:00.123456

datetime2(0) stores seconds only (6 bytes), datetime2(3) stores milliseconds (7 bytes), datetime2(7) stores 100-nanosecond ticks (8 bytes). The dt2_7 column looks like .123456 instead of the full .1234567 for the same pyodbc client-side reason as above — the server still holds all seven digits.

Choose the smallest scale that preserves meaning

  • Business event timestamps: datetime2(0) or datetime2(3) is almost always enough.
  • Market data / trading systems: datetime2(3) for millisecond timestamps.
  • Monotonic sequencing / conflict resolution: datetime2(6) or (7) if you need sub-microsecond precision.
  • Every extra digit of scale costs bytes and index key size, and very few business processes care beyond milliseconds.

Legacy datetime — the rounding trap

Avoid datetime in new code; this is why.

23:59:59.999 rounds forward a full day

This query shows the legacy rounding trap at the end of the day.

datetime rounds to the nearest 1/300 second, and .999 rounds up past midnight.

SELECT
    CAST('2025-01-01 23:59:59.999' AS datetime)     AS legacy_rounds_up,
    CAST('2025-01-01 23:59:59.999' AS datetime2(3)) AS modern_exact;
legacy_rounds_upmodern_exact
2025-01-02 00:00:002025-01-01 23:59:59.999

The left column shows datetime’s infamous rounding bug: the value 23:59:59.999 on January 1st rounds forward to 00:00:00 on January 2nd. Any range-end predicate like col <= '2025-01-01 23:59:59.999' on a datetime column silently includes January 2nd records too.

Legacy datetime + inclusive end dates

The classic production bug is:

  • Column type is datetime.
  • Code uses WHERE col BETWEEN '2025-01-01' AND '2025-01-01 23:59:59.999'.
  • The .999 rounds forward to 2025-01-02 00:00:00, so the range includes every 2025-01-02 event.
  • The report shows too many rows and nobody notices until month-end reconciliation.

datetime2 + half-open range

  • Column type is datetime2 (any precision).
  • Code uses WHERE col >= '2025-01-01' AND col < '2025-01-02'.
  • No rounding, no ambiguity, no off-by-one day errors.
  • This is the universal fix for both the legacy datetime rounding trap and the half-open range pattern (covered at the end of this note).

Legacy smalldatetime — rounds to nearest minute

Four-byte temporal type with minute-level precision; included for completeness.

Rounding behaviour at 29, 30, and 59 seconds

This query shows how smalldatetime rounds to the nearest minute.

smalldatetime rounds to the nearest whole minute at the 30-second mark.

SELECT
    CAST('2025-01-01 12:34:29' AS smalldatetime) AS rounds_down,
    CAST('2025-01-01 12:34:30' AS smalldatetime) AS rounds_up,
    CAST('2025-01-01 12:34:59' AS smalldatetime) AS near_minute_end;
rounds_downrounds_upnear_minute_end
2025-01-01 12:34:002025-01-01 12:35:002025-01-01 12:35:00

The 30-second mark rounds up; 29 seconds rounds down. This is independent of the datetime 1/300-second quirk — smalldatetime drops everything below minute precision entirely. Use it only when working with legacy schemas; never choose it for new columns.

Current Time Functions

Side-by-side comparison

Every current-time function called in the same query so their outputs can be compared directly.

All seven functions

This query captures every current-time function in one result set.

Capture every variant at once. The stoxx Docker container runs at UTC, so the “local” and “UTC” values are identical — the precision and return-type differences are what matter.

SELECT
    GETDATE()              AS getdate_v,
    GETUTCDATE()           AS getutcdate_v,
    SYSDATETIME()          AS sysdatetime_v,
    SYSUTCDATETIME()       AS sysutcdatetime_v,
    CONVERT(varchar(40), SYSDATETIMEOFFSET(), 121) AS sysdatetimeoffset_v,
    CURRENT_TIMESTAMP      AS current_timestamp_v,
    CURRENT_TIMEZONE()     AS current_timezone_v;
getdate_vgetutcdate_vsysdatetime_vsysutcdatetime_vsysdatetimeoffset_vcurrent_timestamp_vcurrent_timezone_v
2026-04-11 12:03:21.432026-04-11 12:03:21.432026-04-11 12:03:21.4413932026-04-11 12:03:21.4413932026-04-11 12:03:21.4413932 +00:002026-04-11 12:03:21.43(UTC) Coordinated Universal Time

Precision: GETDATE vs SYSDATETIME

This query compares the legacy and modern time functions at datetime2(7) precision.

Casting both to a high-precision datetime2(7) shows the legacy function’s missing digits.

SELECT
    CAST(GETDATE() AS datetime2(7))     AS getdate_as_dt2,
    CAST(SYSDATETIME() AS datetime2(7)) AS sysdatetime_as_dt2;
getdate_as_dt2sysdatetime_as_dt2
2026-04-11 12:03:21.432026-04-11 12:03:21.441393

GETDATE() only has three significant fractional digits to begin with, so casting it to datetime2(7) simply pads zeros. SYSDATETIME() has full precision. If two writes happen in the same 1/300-second window, GETDATE() assigns them the same timestamp; SYSDATETIME() distinguishes them.

GETDATE lies about precision

It is tempting to use GETDATE() because it looks like a datetime2 in the output, but:

  • The return type is legacy datetime, which rounds to 1/300 second.
  • Storing many GETDATE() values in a datetime2 column throws away the client-side precision digits because the column has them but the source never produced them.
  • Ordering or deduplicating by a GETDATE()-populated column will produce ties that SYSDATETIME() would have split.

Prefer SYSUTCDATETIME for new writes

  • Precision: full 7-digit scale, no 1/300-second rounding.
  • Zone: UTC removes any ambiguity about the server’s local time or DST state.
  • Interoperability: every downstream system can present UTC in whatever local zone it wants; the reverse is harder.
  • Store SYSUTCDATETIME() in a datetime2(3) or datetime2(7) column depending on how precise your domain needs to be.

DATEADD, DATEDIFF, and DATEDIFF_BIG

DATEADD

Shift a date/time value by a signed count of a given unit.

Basic shifts

This query compares day, month, and year arithmetic on a fixed anchor date.

Add days, months, and years to a calendar date.

SELECT
    DATEADD(DAY,   7,  CAST('2025-01-31' AS date)) AS plus_7_days,
    DATEADD(MONTH, 1,  CAST('2025-01-31' AS date)) AS plus_1_month,
    DATEADD(YEAR, -1,  CAST('2025-01-31' AS date)) AS minus_1_year;
plus_7_daysplus_1_monthminus_1_year
2025-02-072025-02-282024-01-31

Month-end rounding behaviour

This query shows how DATEADD handles invalid target-month days.

Adding a month to January 31st does not produce a “March 3rd”; it produces the last valid day of February.

SELECT
    DATEADD(MONTH, 1, CAST('2025-01-31' AS date)) AS jan31_plus_month,
    DATEADD(MONTH, 1, CAST('2025-03-31' AS date)) AS mar31_plus_month,
    DATEADD(MONTH, 1, CAST('2025-05-31' AS date)) AS may31_plus_month;
jan31_plus_monthmar31_plus_monthmay31_plus_month
2025-02-282025-04-302025-06-30

SQL Server’s rule is: after adding the named unit, if the resulting day would be invalid in the target month, round down to the last valid day of that month. This is the ISO-style behaviour most business users expect.

DATEADD MONTH is not invertible

  • DATEADD(MONTH, 1, '2025-01-31') → 2025-02-28.
  • DATEADD(MONTH, -1, '2025-02-28') → 2025-01-28, not 2025-01-31.
  • Rolling forward one month and back one month is not an identity operation on month-end dates.
  • If you need to track “the last day of month N”, store and regenerate with EOMONTH, not by pushing dates around with DATEADD.

DATEDIFF — boundary counting

Count the number of datepart boundaries crossed between two date/time values.

The year-rollover trap

This query shows why DATEDIFF(YEAR, ...) can overstate elapsed years.

One day apart across a year boundary returns “one year”, “one month”, and “one day” simultaneously.

SELECT
    DATEDIFF(YEAR,  '2024-12-31', '2025-01-01') AS one_day_one_year,
    DATEDIFF(MONTH, '2024-12-31', '2025-01-01') AS one_day_one_month,
    DATEDIFF(DAY,   '2024-12-31', '2025-01-01') AS one_day_one_day;
one_day_one_yearone_day_one_monthone_day_one_day
111

These two dates are 24 hours apart, yet DATEDIFF(YEAR, ...) returns 1. The function counts boundaries crossed: one year boundary (midnight on January 1st), one month boundary, and one day boundary.

DATEDIFF(YEAR, dob, today) is not age

The classic bug: computing age as DATEDIFF(YEAR, dob, today):

  • Someone born on 2000-04-09, asked on 2025-04-08: DATEDIFF(YEAR, ...) returns 25, but they are still 24.
  • The boundary count reflects calendar-year crossings, not “complete years since birth”.
  • The same bug appears in “days employed”, “seconds since last login”, and every other “elapsed time” computation written with the wrong unit.

Exact age in years

This query computes an age-style year difference and the birthday correction.

Subtract one from the DATEDIFF result when the anniversary has not yet been reached in the current year.

DECLARE @dob date = '2000-04-09';
DECLARE @asof date = '2025-04-08';
SELECT
    DATEDIFF(YEAR, @dob, @asof) AS age_datediff_wrong,
    DATEDIFF(YEAR, @dob, @asof)
      - CASE WHEN (MONTH(@asof)*100 + DAY(@asof))
                < (MONTH(@dob) *100 + DAY(@dob))
             THEN 1 ELSE 0 END AS age_exact;
age_datediff_wrongage_exact
2524

The CASE subtracts 1 when the “as-of” month/day is before the birthday month/day. The MONTH*100 + DAY trick packs month and day into a single integer so a plain < comparison does the right thing without bumping into day-of-month edge cases.

Boundary vs elapsed seconds

This query contrasts boundary counting with elapsed-time intuition.

Two timestamps one second apart across an hour boundary return 1 for HOUR, MINUTE, and SECOND alike.

SELECT
    DATEDIFF(HOUR,   '2025-01-01 09:59:59', '2025-01-01 10:00:00') AS hour_boundary,
    DATEDIFF(MINUTE, '2025-01-01 09:59:59', '2025-01-01 10:00:00') AS minute_boundary,
    DATEDIFF(SECOND, '2025-01-01 09:59:59', '2025-01-01 10:00:00') AS elapsed_seconds;
hour_boundaryminute_boundaryelapsed_seconds
111

DATEDIFF(HOUR, 9:59:59, 10:00:00) = 1 even though only one second elapsed — because one HOUR boundary (10:00:00) was crossed. The function is boundary counting, not interval measurement.

Read DATEDIFF as "boundaries crossed"

  • If you need elapsed time between two instants, DATEDIFF(SECOND, ...) or DATEDIFF_BIG(MILLISECOND, ...) is the right tool because the smallest unit collapses to “difference in ticks”.
  • If you need complete calendar years/months/days between two instants (age, tenure), compensate with a CASE guard on the remaining parts as shown above.
  • If you need the literal count of month boundaries crossed, DATEDIFF(MONTH, ...) already does exactly that.

DATEDIFF_BIG — when int overflows

DATEDIFF returns int; for wide ranges of small units, it overflows.

125 years in milliseconds overflows

This query shows the 32-bit DATEDIFF overflow boundary.

DATEDIFF(MILLISECOND, '1900-01-01', '2025-01-01') cannot fit in a 32-bit integer.

SELECT DATEDIFF(MILLISECOND, '1900-01-01', '2025-01-01');
('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. (535) (SQLExecDirectW)')

Error 535 is the overflow — the result would be about 3.9 trillion, far above the 2.1-billion limit of int.

DATEDIFF_BIG — no overflow

This query repeats the same span with DATEDIFF_BIG.

The _BIG variant returns bigint instead of int, so it can represent millisecond-scale differences across centuries.

SELECT DATEDIFF_BIG(MILLISECOND, '1900-01-01', '2025-01-01') AS ms_since_1900;
ms_since_1900
3944678400000

When to reach for DATEDIFF_BIG

  • The interval is wide (multi-year) and the unit is small (seconds, milliseconds, microseconds, nanoseconds).
  • You need monotonic, uniquely-ordered tick counts for audit or sequencing.
  • You are computing Unix timestamps: DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME()) is the standard T-SQL shape.

Date Parts and Components

DATEPART and helper functions

Extract numeric parts from a datetime2 value.

Full part decomposition

This query extracts the year, month, day, and clock parts from one timestamp.

Every datepart pulled from the same timestamp.

DECLARE @d datetime2 = '2025-04-08 14:30:45.1234567';
SELECT
    YEAR(@d)                    AS yr,
    DATEPART(QUARTER, @d)       AS qt,
    MONTH(@d)                   AS mo,
    DAY(@d)                     AS dy,
    DATEPART(WEEK, @d)          AS wk,
    DATEPART(WEEKDAY, @d)       AS wd,
    DATEPART(DAYOFYEAR, @d)     AS doy,
    DATEPART(HOUR, @d)          AS hr,
    DATEPART(MINUTE, @d)        AS mn,
    DATEPART(SECOND, @d)        AS ss,
    DATEPART(MILLISECOND, @d)   AS ms,
    DATEPART(MICROSECOND, @d)   AS us,
    DATEPART(NANOSECOND, @d)    AS ns;
yrqtmodywkwddoyhrmnssmsusns
202524815398143045123123456123456700

DATENAME vs DATEPART

This query compares string and numeric date-part extraction.

DATENAME returns the localized string form of a part, where applicable.

DECLARE @d datetime2 = '2025-04-08';
SELECT
    DATEPART(MONTH, @d)    AS month_int,
    DATENAME(MONTH, @d)    AS month_name,
    DATEPART(WEEKDAY, @d)  AS weekday_int,
    DATENAME(WEEKDAY, @d)  AS weekday_name;
month_intmonth_nameweekday_intweekday_name
4April3Tuesday

The month_name and weekday_name values depend on the session’s current language (SET LANGUAGE). On a french session, the same query returns avril and mardi. For stable output across sessions and deployments, use DATEPART and map the integer to whatever display strings your application requires.

DATENAME is locale-sensitive

  • The string output depends on SET LANGUAGE, which in turn defaults to the login’s default language.
  • Report queries that embed DATENAME(MONTH, ...) in column headers will break if a different user runs them in a different language.
  • Use DATEPART and do the localization in the presentation layer, or use FORMAT(date, 'MMMM', 'en-US') for an explicit culture.

SARGability and date predicates

Why WHERE YEAR(col) = 2025 is slow and what to write instead.

The functions-on-column anti-pattern

This query shows the SARGability cost of wrapping the column in a function.

Wrapping the column in a function disables index seeks.

SELECT COUNT(*)
FROM silver.eurostoxx50_ohlcv
WHERE YEAR([date]) = 2025 AND MONTH([date]) = 1;
row_count
1099

This query returns the correct answer, but the optimizer cannot seek an index on [date] because the predicate references YEAR([date]) and MONTH([date]) — functions applied to the column itself. The engine has to scan every row, evaluate the functions, then filter. On a 67,000-row table this is fine; on a billion-row table it is a disaster.

Functions on the indexed column disable seeks

Any of these patterns forces a scan:

  • WHERE YEAR(col) = 2025
  • WHERE CAST(col AS date) = '2025-01-15'
  • WHERE DATEDIFF(DAY, col, GETDATE()) < 7
  • WHERE FORMAT(col, 'yyyy-MM') = '2025-01'

All four rewrite the predicate so the indexed column is inside a function, which prevents the optimizer from matching it against index key boundaries.

Half-open range fix

This query rewrites the filter so the column stays bare.

Move all the computation to the literal side, leaving the column bare on the left side of the comparison.

SELECT COUNT(*)
FROM silver.eurostoxx50_ohlcv
WHERE [date] >= '2025-01-01'
  AND [date] <  '2025-02-01';
row_count
1099

Same answer, 1099 rows. The difference is that this form is SARGable — the optimizer can seek the index on [date] directly because the column is on the left side of the comparison and no function is wrapping it.

Half-open range is the universal fix

  • col >= start_inclusive AND col < end_exclusive works for every temporal type.
  • It handles time precision correctly (no 23:59:59.999 rounding bug).
  • It is SARGable — the optimizer can use any index on the column.
  • Compute the boundary values once, pass them as parameters or literals, and let the engine do the work.

Parts-Based Constructors

The three constructors

One constructor per temporal return type.

DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEOFFSETFROMPARTS

This query builds equivalent values with parts-based constructors.

Build each type from integer inputs.

SELECT
    DATEFROMPARTS(2025, 4, 8) AS safe_date,
    DATETIME2FROMPARTS(2025, 4, 8, 14, 30, 0, 1234567, 7) AS safe_dt2,
    CONVERT(varchar(40),
        DATETIMEOFFSETFROMPARTS(2025, 4, 8, 14, 30, 0, 0, 2, 0, 0),
        121) AS safe_dto;
safe_datesafe_dt2safe_dto
2025-04-082025-04-08 14:30:00.1234562025-04-08 14:30:00 +02:00

Invalid combinations raise

This query shows that invalid date parts fail fast.

Invalid date components produce a hard error — unlike string parsing which can quietly misinterpret them.

SELECT DATEFROMPARTS(2025, 2, 30);
('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot construct data type date, some of the arguments have values which are not valid. (289) (SQLExecDirectW)')

February 30th does not exist and the constructor raises error 289. Compare this to parsing '2025-02-30' as a string, which some locales would accept and silently convert to March 2nd or 3rd.

Prefer parts constructors over string concat

  • DATEFROMPARTS(yr, mo, dy) fails loudly on invalid components.
  • CAST(CONCAT(yr, '-', mo, '-', dy) AS date) succeeds in surprising ways and can hide bad data.
  • The parts form does not depend on the session’s DATEFORMAT or LANGUAGE.
  • It is also slightly faster because there is no string allocation or parse step.

EOMONTH and Business Boundaries

EOMONTH

Return the last day of a given month, optionally offset by N months.

Basic and offset EOMONTH

This query compares month-end calculation with and without an offset.

Last day of this month, next month, a leap-year February, and 11 months prior.

SELECT
    EOMONTH('2025-02-11')       AS feb_end,
    EOMONTH('2025-02-11', 1)    AS next_month_end,
    EOMONTH('2024-02-11')       AS leap_feb,
    EOMONTH('2025-12-15', -11)  AS jan_end_prior_year;
feb_endnext_month_endleap_febjan_end_prior_year
2025-02-282025-03-312024-02-292025-01-31

First-of-period patterns

Build the start of the current month, quarter, and year.

First of month, quarter, year

This query derives period starts from the same anchor date.

The three canonical period-start patterns.

DECLARE @today date = '2025-04-08';
SELECT
    DATEFROMPARTS(YEAR(@today), MONTH(@today), 1)     AS first_of_month,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @today), 0) AS first_of_quarter,
    DATEFROMPARTS(YEAR(@today), 1, 1)                 AS first_of_year;
first_of_monthfirst_of_quarterfirst_of_year
2025-04-012025-04-01 00:00:002025-01-01

Last-of-period is EOMONTH or first-of-next-period minus one

  • Last of month: EOMONTH(x).
  • Last of quarter: EOMONTH(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, x) + 1, -1)) — or just compute first-of-next-quarter and use < first_of_next in a half-open range.
  • Last of year: DATEFROMPARTS(YEAR(x), 12, 31).
  • In most query contexts, the “first of next period” form is cleaner than the “last of this period” form because it composes directly with the half-open range pattern: col >= start AND col < next_start.

ISO 8601 Literals and Safe Parsing

The locale trap

Two identical strings, two different parses.

DATEFORMAT mdy

This query parses an ambiguous literal under U.S. month/day/year rules.

Session set to US-style month/day/year.

SET DATEFORMAT mdy;
SELECT CAST('04/08/2025' AS date) AS mdy_reading;
mdy_reading
2025-04-08

DATEFORMAT dmy

This query parses the same literal under day/month/year rules.

Same string, European-style day/month/year.

SET DATEFORMAT dmy;
SELECT CAST('04/08/2025' AS date) AS dmy_reading;
dmy_reading
2025-08-04

The same string '04/08/2025' is interpreted as April 8 on an mdy session and August 4 on a dmy session — a four-month error that silently flips every value.

Locale-dependent literals are a data integrity risk

  • '04/08/2025' changes meaning based on SET DATEFORMAT.
  • '04-08-2025' is also locale-dependent despite the hyphens.
  • Production servers across regions frequently have different default languages.
  • A query that works correctly in development can silently produce wrong results in production.

ISO 8601 literals are unambiguous

  • '2025-04-08' — always April 8th, 2025, regardless of DATEFORMAT.
  • '2025-04-08T14:30:00' — same value with time, unambiguous across every session.
  • '2025-04-08T14:30:00+02:00' — offset-aware literal for datetimeoffset.
  • These forms are ISO 8601 standard and are the only date literals you should write in code you intend to ship.

ISO literal example

This query shows the canonical ISO 8601 literal form.

A canonical ISO datetime literal cast to datetime2.

SELECT CAST('2025-04-08T14:30:00' AS datetime2) AS iso_literal;
iso_literal
2025-04-08 14:30:00

Defensive parsing

When you cannot control the input format, use TRY_CONVERT to parse without raising.

TRY_CONVERT returns NULL on failure

This query shows how invalid inputs become NULL instead of errors.

Unparseable inputs produce NULL instead of an error, letting the query continue.

SELECT
    TRY_CONVERT(date, '2025-13-40')         AS bad_date,
    TRY_CONVERT(date, '2025-04-08')         AS good_date,
    TRY_CONVERT(datetime2, 'not a date')    AS not_parseable;
bad_dategood_datenot_parseable
NULL2025-04-08NULL

TRY_CONVERT never raises on invalid input — it returns NULL instead. This makes it the right tool for ETL quarantine logic: keep the rows with a non-NULL parsed value, route the NULL rows into a quarantine table for manual review.

TRY_CONVERT vs TRY_PARSE

  • TRY_CONVERT uses the same rules as CONVERT (and CAST) but suppresses errors.
  • TRY_PARSE uses .NET’s DateTime.Parse and accepts a USING <culture> clause for locale-specific parsing; it is slower and should only be used when you need .NET’s parsing flexibility.
  • For ISO-formatted inputs and most other cases, prefer TRY_CONVERT.

AT TIME ZONE

Attaching an offset to a naive value

Treat a naive datetime2 as if it were already in the target zone, and tag it with that zone’s offset.

AT TIME ZONE ‘UTC’

This query attaches a UTC offset to a naive timestamp.

Wrap a naive value and declare “this was always UTC”.

SELECT CONVERT(varchar(40),
    CAST('2025-03-10T15:30:00' AS datetime2) AT TIME ZONE 'UTC',
    121) AS attach_utc;
attach_utc
2025-03-10 15:30:00.0000000 +00:00

The wall-clock value is unchanged (15:30:00), but the result is now a datetimeoffset with +00:00 attached. No shift occurred — SQL Server interpreted the naive input as already being in the UTC zone.

Converting between zones

Chain two AT TIME ZONE calls to shift an instant across zones.

UTC to Paris

This query converts a UTC instant into Paris local time.

First attach the zone (step 1), then convert to the target zone (step 2).

SELECT CONVERT(varchar(40),
    CAST('2025-03-10T15:30:00' AS datetime2) AT TIME ZONE 'UTC'
                                             AT TIME ZONE 'Romance Standard Time',
    121) AS paris_time;
paris_time
2025-03-10 16:30:00.0000000 +01:00

On 2025-03-10, Paris was still on standard time (+01:00 — DST starts on the last Sunday of March). The conversion adds one hour to 15:30 UTC and produces 16:30 +01:00, which is the correct Paris local time at that instant.

sys.time_zone_info

The catalog view listing every zone name SQL Server recognizes.

Sample zone offsets

This query lists a few Windows zone names and their current offsets.

A handful of common zones and their current UTC offsets.

SELECT TOP (5) name, current_utc_offset, is_currently_dst
FROM sys.time_zone_info
WHERE name IN (
    'UTC',
    'Romance Standard Time',
    'Central Europe Standard Time',
    'Eastern Standard Time',
    'Tokyo Standard Time'
)
ORDER BY current_utc_offset;
namecurrent_utc_offsetis_currently_dst
UTC+00:00False
Romance Standard Time+02:00True
Central Europe Standard Time+02:00True
Tokyo Standard Time+09:00False
Eastern Standard Time-04:00True

The three columns are:

  • name — the Windows zone identifier you pass to AT TIME ZONE.
  • current_utc_offset — the zone’s current offset, which changes with DST.
  • is_currently_dst — whether the zone is currently in daylight saving time. This query was run in April 2026 when European zones are in DST (so Paris/Romance shows +02:00 even though its standard offset is +01:00).

Windows zone names, not IANA names

SQL Server does not accept IANA zone names like Europe/Paris:

  • Pass Romance Standard Time instead of Europe/Paris.
  • Pass Central Europe Standard Time instead of Europe/Berlin.
  • Pass Eastern Standard Time instead of America/New_York.
  • If you need to cross-reference between IANA and Windows, the Linux-to-Windows mapping in the SQL Server on Linux docs is the authoritative table.

DST and Boundary Pitfalls

Spring forward — non-existent local time

At 02:30 local on spring-forward day, the clock has already jumped to 03:30.

Paris spring-forward 2025

This query shows how SQL Server handles the missing spring-forward hour.

March 30, 2025 was the European DST change day. 02:30 Paris local does not exist that day.

SELECT
    CONVERT(varchar(40),
        CAST('2025-03-30T02:30:00' AS datetime2) AT TIME ZONE 'Romance Standard Time',
        121) AS paris_gap_local,
    CONVERT(varchar(40),
        CAST('2025-03-30T00:30:00' AS datetime2) AT TIME ZONE 'UTC'
                                                 AT TIME ZONE 'Romance Standard Time',
        121) AS paris_after_utc;
paris_gap_localparis_after_utc
2025-03-30 03:30:00.0000000 +02:002025-03-30 01:30:00.0000000 +01:00

Read the left column: the input was 2025-03-30 02:30:00 as if it were Paris local time. AT TIME ZONE applies the after-DST offset (+02:00) and produces 03:30:00 +02:00 — because 02:30 didn’t exist, the engine treats it as the time one hour later, 03:30, which does exist.

The right column shows the correct way to handle the transition: start from UTC (00:30 UTC), which is unambiguous, and convert to Paris. At that instant Paris was still on standard time (+01:00), so the result is 01:30 +01:00 — the correct local wall-clock value half an hour before the DST jump.

Non-existent local time cannot be round-tripped

If you store local wall-clock time in a naive column and the user picks “02:30 on the DST change day”:

  • That value does not correspond to a real instant.
  • AT TIME ZONE will silently snap it forward to 03:30 +02:00.
  • The user’s intent is lost — there is no way to tell whether they meant “02:30 before DST, which is actually 03:30 after” or “they misread a clock”.
  • The only correct storage for event timestamps is UTC, converted to local time at read time.

Fall back — ambiguous local time

At 02:30 local on fall-back day, the clock says 02:30 twice — once at +02:00 and once at +01:00.

Paris fall-back 2025

This query shows how SQL Server distinguishes the repeated fall-back hour.

October 26, 2025 was the European DST exit day. 02:30 Paris local happened twice.

SELECT
    CONVERT(varchar(40),
        CAST('2025-10-26T02:30:00' AS datetime2) AT TIME ZONE 'Romance Standard Time',
        121) AS paris_ambiguous_before,
    CONVERT(varchar(40),
        CAST('2025-10-26T01:30:00' AS datetime2) AT TIME ZONE 'UTC'
                                                 AT TIME ZONE 'Romance Standard Time',
        121) AS paris_0130_utc;
paris_ambiguous_beforeparis_0130_utc
2025-10-26 02:30:00.0000000 +02:002025-10-26 02:30:00.0000000 +01:00

Both columns show 02:30:00 on 2025-10-26. They are different instants:

  • The left column is the first 02:30 (still on DST, offset +02:00) — the naive input was treated as local time before the DST change, per the documented rule.
  • The right column is the second 02:30 (after DST ended, offset +01:00) — derived from 01:30 UTC.

Both wall-clock strings are identical. Only the offset distinguishes them, and only the datetimeoffset representation preserves that distinction.

Naive local time in the fall-back hour is unrecoverable

If an audit log stores local wall-clock time as a datetime2 during the fall-back hour:

  • Two events one hour apart can have identical timestamps.
  • There is no way to sort them correctly, deduplicate them, or compute elapsed time between them.
  • The only defense is to store every timestamp as datetimeoffset or as UTC datetime2, and convert on display.

BETWEEN on datetime2

The BETWEEN operator on a datetime2 column does not cover a full day’s data.

BETWEEN misses the tail of the end day

This query demonstrates why BETWEEN drops late-day rows.

A naive “BETWEEN start AND end” range excludes every event after midnight on the end day.

DECLARE @events TABLE (ts datetime2);
INSERT INTO @events VALUES
    ('2025-04-30 00:00:00'),
    ('2025-04-30 12:00:00'),
    ('2025-04-30 23:59:59.9999999'),
    ('2025-05-01 00:00:00');
SELECT ts
FROM @events
WHERE ts BETWEEN '2025-04-01' AND '2025-04-30';
ts
2025-04-30 00:00:00

Only the midnight event on April 30 is returned. The 12:00 and 23:59:59.9999999 events are missed — BETWEEN ... AND '2025-04-30' is equivalent to <= '2025-04-30 00:00:00', which excludes the rest of the day.

BETWEEN on datetime2 excludes most of the end day

  • The literal '2025-04-30' is interpreted as 2025-04-30 00:00:00.0000000.
  • BETWEEN '2025-04-01' AND '2025-04-30' matches col >= '2025-04-01' AND col <= '2025-04-30 00:00:00'.
  • Every event with a non-zero time on April 30 is silently excluded.
  • This is the most common production reporting bug in the entire temporal feature set.

Half-open range covers the full day

This query uses the half-open predicate to keep every row on the target day.

Use >= start AND < next_start to include every event in the target period.

DECLARE @events TABLE (ts datetime2);
INSERT INTO @events VALUES
    ('2025-04-30 00:00:00'),
    ('2025-04-30 12:00:00'),
    ('2025-04-30 23:59:59.9999999'),
    ('2025-05-01 00:00:00');
SELECT ts
FROM @events
WHERE ts >= '2025-04-01'
  AND ts <  '2025-05-01';
ts
2025-04-30 00:00:00
2025-04-30 12:00:00
2025-04-30 23:59:59.999999

All three April 30 events are included, and the May 1 midnight event is correctly excluded. The half-open range handles every precision scale — nothing changes if the column is datetime2(3) vs datetime2(7).

Half-open range is the only correct date filter

  • col >= start AND col < end works on every temporal type.
  • It handles all precision scales without thinking about trailing 9s.
  • It is SARGable (no functions on the column side).
  • It composes naturally with first-of-next-period patterns.

Practical Date Patterns

Rolling windows

“Last N days” from a fixed anchor date.

Last 7 days against a real table

This query pulls a real seven-day slice from silver.eurostoxx50_ohlcv.

Pull every row from the last week of trading through April 7, 2026.

SELECT TOP (5) symbol, [date], [close]
FROM silver.eurostoxx50_ohlcv
WHERE [date] >= '2026-04-01'
  AND [date] <  '2026-04-08'
ORDER BY [date] DESC, symbol;
symboldateclose
ABI.BR2026-04-0761.62
AD.AS2026-04-0741.69
ADS.DE2026-04-07130.85
ADYEN.AS2026-04-07844.2
AI.PA2026-04-07181.5

The half-open predicate [date] >= '2026-04-01' AND [date] < '2026-04-08' captures exactly the April 1 through April 7 trading days. Switching the anchor to a parameter and computing it with DATEADD(DAY, -7, CAST(SYSUTCDATETIME() AS date)) generalizes the pattern for any “last N days from now” query.

Period boundaries

Current-month window built from a parameterized anchor date.

Current month from an anchor

This query computes an inclusive month start and exclusive month end.

Compute the first day of the month and the first day of the next month.

DECLARE @now date = '2025-04-08';
SELECT
    DATEFROMPARTS(YEAR(@now), MONTH(@now), 1) AS month_start,
    DATEADD(MONTH, 1,
        DATEFROMPARTS(YEAR(@now), MONTH(@now), 1)) AS month_end_exclusive;
month_startmonth_end_exclusive
2025-04-012025-05-01

Use the two computed values directly in a half-open predicate: WHERE col >= @month_start AND col < @month_end_exclusive. This is SARGable, precision-safe, and DST-safe.

Real datetime2 column in silver.index_dim

A production SCD-2 dimension table with valid_from / valid_to / is_current columns demonstrates the audit timestamp pattern.

SELECT TOP (3) symbol, valid_from, valid_to, is_current
FROM silver.index_dim
WHERE is_current = 1
ORDER BY symbol;
symbolvalid_fromvalid_tois_current
0388.HK2026-03-04 22:11:36.30016NULLTrue
1299.HK2026-03-04 22:11:36.263401NULLTrue
1810.HK2026-03-04 22:11:36.328825NULLTrue

This is the canonical Slowly Changing Dimension Type 2 shape: valid_from is the UTC timestamp the record became active (populated by SYSUTCDATETIME() in the ETL), valid_to is NULL for the currently-active record, and is_current is a convenience flag denormalized from the valid_to IS NULL predicate. The datetime2 column holds sub-millisecond precision to allow monotonic ordering of same-second changes.

SCD-2 timestamp patterns

  • Always populate valid_from with SYSUTCDATETIME(), never GETDATE().
  • Use datetime2(7) (or at minimum datetime2(6)) so simultaneous updates do not collide.
  • Current-record predicates should use is_current = 1 for performance (indexable) and valid_to IS NULL for correctness checks.
  • Point-in-time queries use WHERE @asof_ts >= valid_from AND (@asof_ts < valid_to OR valid_to IS NULL) — half-open range as always.

Practical Guidance

Defaults for new code

  • Type: datetime2(3) for most timestamps; datetime2(7) when monotonicity matters; datetimeoffset(3) when the offset is a business fact; date when you do not need time.
  • Write timestamp: always SYSUTCDATETIME(), never GETDATE().
  • Literals: ISO 8601 only ('2025-04-08', '2025-04-08T14:30:00', '2025-04-08T14:30:00+02:00').
  • Construction: DATEFROMPARTS / DATETIME2FROMPARTS / DATETIMEOFFSETFROMPARTS, never string concatenation.
  • Date filters: half-open range (>= start AND < end), never BETWEEN, never functions on the column.
  • Time zones: AT TIME ZONE with Windows zone names from sys.time_zone_info, never manual offset arithmetic.
  • Storage: UTC in the column, conversion to local time at the presentation layer.
  • Age / tenure: DATEDIFF(YEAR, ...) with a CASE compensation for the birthday/anniversary not yet reached.

Production-grade checklist

Before shipping any temporal code:

  • Have you used ISO 8601 literals everywhere?
  • Are all date filters half-open (>= AND <)?
  • Are there any WHERE YEAR(col), WHERE CAST(col AS date), or WHERE DATEDIFF(...) < N patterns that should be rewritten for SARGability?
  • Is the column the column type you think it is — datetime2 and not legacy datetime?
  • If you store local time, does AT TIME ZONE correctly handle DST transitions for the data you expect to land in those hours?
  • Does the audit log use datetimeoffset or UTC datetime2 + SYSUTCDATETIME()?

Graph View

Backlinks