Database SQL Fundamentals — #20

T-SQL Date and Time Functions: SYSDATETIME and SYSUTCDATETIME, DATEADD, DATEDIFF, EOMONTH, DATEFROMPARTS and AT TIME ZONE

Mert Özen Nov 15, 2026 16 min 9 views

T-SQL date functions are sensitive to session settings and types: in a Turkish session '2026-04-03' becomes March 4 as DATETIME, and DATEDIFF counts boundaries, not durations, always starting weeks on Sunday. With real output and PostgreSQL counterparts we cover SYSUTCDATETIME, EOMONTH, DATEFROMPARTS, ISO_WEEK, the .997 trap of BETWEEN, two-step AT TIME ZONE from UTC to Istanbul time and DST transitions.

Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database; the PostgreSQL comparisons were run on PostgreSQL 18.7.

Guess First: Is an ISO Date Always Safe?

In post 07 we recommended writing date literals in ISO 8601 form, like '2025-03-31'. Let us switch the session language to Turkish and convert the same literal to two different types. Are all four columns April 3, 2026?

SET LANGUAGE Turkish;
SELECT CAST('2026-04-03' AS DATE) AS as_date,
       CAST('2026-04-03' AS DATETIME) AS as_datetime,
       CAST('20260403' AS DATETIME) AS unseparated,
       CAST('2026-04-03T00:00:00' AS DATETIME) AS iso_with_t;
SET LANGUAGE us_english;

Dil ayarı 'Türkçe' olarak değiştirildi.
as_date     as_datetime              unseparated              iso_with_t
----------  -----------------------  -----------------------  -----------------------
2026-04-03  2026-03-04 00:00:00.000  2026-04-03 00:00:00.000  2026-04-03 00:00:00.000

(1 rows affected)
Changed language setting to us_english.

No. DATE read the literal correctly, but the old DATETIME type read the same text as March 4. The Turkish language setting makes the date order day-month-year (dmy), and DATETIME interprets the hyphenated yyyy-mm-dd form according to that order. The unseparated '20260403' and the T-separated '2026-04-03T00:00:00' are read the same way in every language. Had the day been greater than 12 ('2026-04-13'), you would have got an error, and in Turkish: "varchar veri türünden bir datetime veri türüne dönüştürme aralık dışı bir değerle sonuçlandı." If Turkish is selected in the application's connection string or as the login's default language, this difference shows up in production.

In the previous post we covered string functions. This post is about date and time functions: getting the current time, building dates, the boundary-counting logic of DATEDIFF, the effect of the language and DATEFIRST settings, half-open ranges and AT TIME ZONE. The examples come from the orders table of Bookstore, the online bookstore used as the shared example across this site's series, which stores UTC.

Current Time and Building Dates

T-SQL has six expressions that return "now", and their types differ:

SELECT 'GETDATE()' AS fn, CAST(SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType') AS VARCHAR(20)) AS base_type, CONVERT(VARCHAR(40), GETDATE(), 121) AS value
UNION ALL SELECT 'CURRENT_TIMESTAMP', CAST(SQL_VARIANT_PROPERTY(CURRENT_TIMESTAMP, 'BaseType') AS VARCHAR(20)), CONVERT(VARCHAR(40), CURRENT_TIMESTAMP, 121)
UNION ALL SELECT 'SYSDATETIME()', CAST(SQL_VARIANT_PROPERTY(SYSDATETIME(), 'BaseType') AS VARCHAR(20)), CONVERT(VARCHAR(40), SYSDATETIME(), 121)
UNION ALL SELECT 'SYSUTCDATETIME()', CAST(SQL_VARIANT_PROPERTY(SYSUTCDATETIME(), 'BaseType') AS VARCHAR(20)), CONVERT(VARCHAR(40), SYSUTCDATETIME(), 121)
UNION ALL SELECT 'SYSDATETIMEOFFSET()', CAST(SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType') AS VARCHAR(20)), CONVERT(VARCHAR(40), SYSDATETIMEOFFSET(), 121)
UNION ALL SELECT 'CURRENT_DATE', CAST(SQL_VARIANT_PROPERTY(CURRENT_DATE, 'BaseType') AS VARCHAR(20)), CONVERT(VARCHAR(40), CURRENT_DATE, 121);

fn                   base_type       value
-------------------  --------------  ----------------------------------
GETDATE()            datetime        2026-11-14 22:48:04.770
CURRENT_TIMESTAMP    datetime        2026-11-14 22:48:04.770
SYSDATETIME()        datetime2       2026-11-14 22:48:04.7722955
SYSUTCDATETIME()     datetime2       2026-11-14 22:48:04.7722955
SYSDATETIMEOFFSET()  datetimeoffset  2026-11-14 22:48:04.7722955 +00:00
CURRENT_DATE         date            2026-11-14

(6 rows affected)

GETDATE() and the standard CURRENT_TIMESTAMP are the same thing: the server's local time in the old DATETIME type, with a precision of a third of a millisecond (.770). SYSDATETIME() returns local time as DATETIME2(7), SYSUTCDATETIME() returns UTC, and SYSDATETIMEOFFSET() returns local time with its offset. CURRENT_DATE arrived with 2025 (post 18). The lab container's time zone is UTC, so local and UTC values are the same here; on a server in Istanbul, SYSDATETIME() and SYSUTCDATETIME() are three hours apart. Bookstore stores UTC, so write a new row's time with SYSUTCDATETIME(); GETDATE() produces a value that depends on where the server is.

The most common functions for building dates are EOMONTH, the DATEFROMPARTS family and DATETRUNC, which arrived with 2022 (post 17):

SELECT EOMONTH('2026-02-10') AS end_feb, EOMONTH('2026-02-10', 1) AS end_mar, EOMONTH('2028-02-10') AS leap_feb,
       DATEADD(DAY, 1, EOMONTH('2026-06-20', -1)) AS first_of_month,
       DATEFROMPARTS(2026, 6, 1) AS from_parts,
       DATETIME2FROMPARTS(2026, 6, 1, 9, 30, 0, 0, 0) AS dt2_from_parts,
       DATETRUNC(MONTH, CAST('2026-06-20 14:05' AS DATETIME2(0))) AS trunc_month;

end_feb     end_mar     leap_feb    first_of_month  from_parts  dt2_from_parts       trunc_month
----------  ----------  ----------  --------------  ----------  -------------------  -------------------
2026-02-28  2026-03-31  2028-02-29  2026-06-01      2026-06-01  2026-06-01 09:30:00  2026-06-01 00:00:00

The second argument of EOMONTH shifts the month; it knows about leap years. For "first day of the month" people used to write DATEADD(DAY, 1, EOMONTH(x, -1)); since 2022 DATETRUNC(MONTH, x) reads better. DATEFROMPARTS returns NULL if any part is NULL, and raises an error for an invalid date:

SELECT DATEFROMPARTS(2026, 2, 30) AS bad_date;

Msg 289, Level 16, State 1, Line 1
Cannot construct data type date, some of the arguments have values which are not valid.

We saw how DATEADD behaves at month end (January 31 + 1 month = February 28) in post 07; in PostgreSQL date '2026-01-31' + interval '1 month' gives the same result, 2026-02-28.

DATEDIFF: It Counts Boundaries, Not Durations

In post 07 we saw that DATEDIFF(YEAR, '2025-12-31', '2026-01-01') returns 1, meaning DATEDIFF counts the boundaries crossed between two dates. This has two more consequences. The first is age:

DECLARE @birth DATE = '2000-12-31', @today DATE = '2026-06-20';
SELECT DATEDIFF(YEAR, @birth, @today) AS datediff_year,
       DATEDIFF(YEAR, @birth, @today)
         - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @birth, @today), @birth) > @today THEN 1 ELSE 0 END AS age;

datediff_year  age
-------------  ---
26             25

Someone born on December 31, 2000 is 25 on June 20, 2026, but DATEDIFF counted 26 year boundaries. The correct calculation adds the year difference to the birth date and subtracts one if that passes today. In PostgreSQL age(date '2026-06-20', date '2000-12-31') returns "25 years 5 mons 20 days"; the year part is right.

The second is weeks. DATEDIFF(WEEK, ...) ignores the session's DATEFIRST setting and always assumes the week starts on Sunday:

SET DATEFIRST 1;
SELECT DATEPART(WEEKDAY, '2026-06-20') AS weekday_monday_first,
       DATEDIFF(WEEK, '2026-06-20', '2026-06-21') AS week_diff_sat_sun,
       DATEDIFF(WEEK, '2026-06-21', '2026-06-22') AS week_diff_sun_mon;
SET DATEFIRST 7;

weekday_monday_first  week_diff_sat_sun  week_diff_sun_mon
--------------------  -----------------  -----------------
6                     1                  0

We said the week starts on Monday and DATEPART followed it: Saturday is day 6. But DATEDIFF counted one week from Saturday to Sunday and zero weeks from Sunday to Monday. Microsoft Learn describes this as a deliberate choice, so that the function's result does not depend on a session setting. If you ask "how many weeks passed" with Monday-based weeks, divide the day difference by 7 or compare week starts computed with DATETRUNC(ISO_WEEK, ...).

A third limit is the result type. DATEDIFF returns INT; even the milliseconds in one year do not fit:

SELECT DATEDIFF(MILLISECOND, '2025-01-01', '2026-01-01') AS ms_in_2025;

Msg 535, Level 16, State 1, Line 1
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.

SELECT DATEDIFF_BIG(MILLISECOND, '2025-01-01', '2026-01-01') AS ms_in_2025,
       DATEDIFF_BIG(MILLISECOND, '1970-01-01', '2026-01-01') AS unix_ms;

ms_in_2025   unix_ms
-----------  -------------
31536000000  1767225600000

DATEDIFF_BIG works the same way but returns BIGINT. The second column produces a Unix timestamp in milliseconds, like JavaScript's Date.now(); the other direction, turning milliseconds into a date, we did in post 18 with the BIGINT argument that DATEADD accepts in 2025.

DATEPART, DATENAME, SET LANGUAGE and SET DATEFIRST

DATEPART returns a part of the date as a number, DATENAME as text. Both depend on session settings:

SET LANGUAGE Turkish;
SELECT DATENAME(MONTH, '2026-06-20') AS month_name, DATENAME(WEEKDAY, '2026-06-20') AS day_name,
       DATEPART(WEEKDAY, '2026-06-20') AS weekday_no, @@DATEFIRST AS datefirst;
SET LANGUAGE us_english;
SELECT DATENAME(MONTH, '2026-06-20') AS month_name, DATENAME(WEEKDAY, '2026-06-20') AS day_name,
       DATEPART(WEEKDAY, '2026-06-20') AS weekday_no, @@DATEFIRST AS datefirst;

Dil ayarı 'Türkçe' olarak değiştirildi.
month_name  day_name   weekday_no  datefirst
----------  ---------  ----------  ---------
Haziran     Cumartesi  6           1

(1 rows affected)
Changed language setting to us_english.
month_name  day_name  weekday_no  datefirst
----------  --------  ----------  ---------
June        Saturday  7           7

(1 rows affected)

The same Saturday is day 6 of the week in a Turkish session and day 7 in an English one. The language setting changes DATEFIRST too: in Turkish the week starts on Monday, in us_english on Sunday. If a report counts weekend orders with WHERE DATEPART(WEEKDAY, ordered_at) IN (1, 7), its result changes with the login's language. Do not use day names or numbers as business rules; if you need them, set SET DATEFIRST explicitly at the start of the query, or use DATENAME only for text shown on screen.

There are two options for the week number, and they diverge at year end:

SELECT d AS dt, DATEPART(WEEK, d) AS week_no, DATEPART(ISO_WEEK, d) AS iso_week, DATENAME(WEEKDAY, d) AS day_name
FROM (VALUES (CAST('2025-12-28' AS DATE)), ('2025-12-29'), ('2025-12-31'), ('2026-01-01'), ('2026-01-04'), ('2026-01-05')) AS v(d);

dt          week_no  iso_week  day_name
----------  -------  --------  ---------
2025-12-28  53       52        Sunday
2025-12-29  53       1         Monday
2025-12-31  53       1         Wednesday
2026-01-01  1        1         Thursday
2026-01-04  2        1         Sunday
2026-01-05  2        2         Monday

WEEK starts every year from the week of January 1 and follows DATEFIRST; the last days of 2025 are week 53, and January 4, 2026 is week 2. ISO_WEEK counts according to ISO 8601: weeks start on Monday, and the week containing the year's first Thursday is week 1. That is why December 29, 2025 is week 1 of 2026. Reports in Europe usually expect the ISO week number; PostgreSQL's extract(week FROM ...) returns the ISO week too.

Date Ranges: Half-Open Ranges and the BETWEEN Trap

Let us count the orders of March 2025 in three ways:

SELECT
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at BETWEEN '2025-03-01' AND '2025-03-31') AS between_dates,
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at BETWEEN '2025-03-01' AND '2025-03-31 23:59:59.997') AS between_997,
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01') AS half_open;

between_dates  between_997  half_open
-------------  -----------  ---------
25             28           28

'2025-03-31' means midnight at the start of March 31; the three orders on the last day of the month (at 12:01, 13:52 and 21:48) were left out. We saw this trap in post 06. The common "fix" is to make the end 23:59:59.997; it gave the right result on this data, but whether it is right depends on the column's type. DATETIME rounds values to 1/300 of a second, and .999 rolls over to the next day:

DECLARE @t TABLE (event_at DATETIME);
INSERT INTO @t VALUES ('2025-03-31 23:59:59.997'), ('2025-04-01 00:00:00');
SELECT COUNT(*) AS march_999 FROM @t WHERE event_at BETWEEN '2025-03-01' AND '2025-03-31 23:59:59.999';

march_999
---------
2

The row at midnight on April 1 was counted in March. On a more precise DATETIME2(7) column the opposite happens:

DECLARE @t2 TABLE (event_at DATETIME2(7));
INSERT INTO @t2 VALUES ('2025-03-31 23:59:59.9985'), ('2025-04-01 00:00:00');
SELECT COUNT(*) AS march_997_dt2 FROM @t2 WHERE event_at BETWEEN '2025-03-01' AND '2025-03-31 23:59:59.997';

march_997_dt2
-------------
0

The row at 23:59:59.9985 on March 31 belongs to March, but it was lost because it is greater than .997. A half-open range (>= start AND < next start) works correctly for every type and every precision; instead of computing the end of the range, you write the start of the next period. It is also sargable, as we saw in post 07: YEAR(ordered_at) = 2025 scans the index, while this form seeks.

AT TIME ZONE: UTC and Istanbul Time

SQL Server knows time zones by their Windows names; it uses the same names on Linux, and the list is in sys.time_zone_info:

SELECT name, current_utc_offset, is_currently_dst
FROM sys.time_zone_info
WHERE name IN ('UTC', 'Turkey Standard Time', 'W. Europe Standard Time', 'GMT Standard Time')
ORDER BY name;

name                     current_utc_offset  is_currently_dst
-----------------------  ------------------  ----------------
GMT Standard Time        +00:00              0
Turkey Standard Time     +03:00              0
UTC                      +00:00              0
W. Europe Standard Time  +01:00              0

Turkey has not observed daylight saving time since 2016 and is +03:00 all year. Bookstore's customers in Germany and the Netherlands are in W. Europe Standard Time, whose offset is +02:00 in summer. IANA names are not accepted: AT TIME ZONE 'Europe/Istanbul' fails with Msg 9820: The time zone parameter 'Europe/Istanbul' provided to AT TIME ZONE clause is invalid.

The rule of AT TIME ZONE is this: if the input has no offset (DATETIME2), it assumes the value is already in that time zone and only adds the offset; if the input is DATETIMEOFFSET, it converts the value to that time zone. So converting a value stored in UTC to Istanbul time takes two steps:

SELECT order_id, ordered_at,
       ordered_at AT TIME ZONE 'UTC' AS utc_offset,
       ordered_at AT TIME ZONE 'UTC' AT TIME ZONE 'Turkey Standard Time' AS istanbul,
       ordered_at AT TIME ZONE 'Turkey Standard Time' AS wrong_way
FROM dbo.orders
WHERE order_id = 263;

order_id  ordered_at           utc_offset                  istanbul                    wrong_way
--------  -------------------  --------------------------  --------------------------  --------------------------
263       2025-12-31 23:06:27  2025-12-31 23:06:27 +00:00  2026-01-01 02:06:27 +03:00  2025-12-31 23:06:27 +03:00

The right way is to first say "this value is UTC" and then convert to Istanbul: 02:06 on New Year's night. The last column is written in one step and is wrong: the clock time stayed the same and only +03:00 was attached; this actually describes a moment three hours earlier. A result that raises no error and looks right at first glance.

On the days when clocks move forward or back, the meaning of a local time without an offset is ambiguous:

SELECT CAST('2026-03-29 01:30' AS DATETIME2(0)) AT TIME ZONE 'W. Europe Standard Time' AS before_gap,
       CAST('2026-03-29 02:30' AS DATETIME2(0)) AT TIME ZONE 'W. Europe Standard Time' AS in_gap,
       CAST('2026-10-25 02:30' AS DATETIME2(0)) AT TIME ZONE 'W. Europe Standard Time' AS ambiguous;

before_gap                  in_gap                      ambiguous
--------------------------  --------------------------  --------------------------
2026-03-29 01:30:00 +01:00  2026-03-29 03:30:00 +02:00  2026-10-25 02:30:00 +02:00

On March 29 there is no 02:30 in Berlin; SQL Server moved it an hour ahead to 03:30 +02:00. On October 25, 02:30 happens twice; as Microsoft Learn documents, the offset before the change (+02:00) was chosen. This is the real reason to store UTC: there are no ambiguous times in UTC, the ambiguity appears only when going from local time back to UTC. Because time zone rules are kept outside SQL Server and change from time to time, AT TIME ZONE is classed as nondeterministic; that is why it cannot be used in a persisted computed column or an indexed view.

Bookstore Reports

Let us take June 20, 2026 as the data's "today". "This month's orders" with a half-open range:

DECLARE @today DATE = '2026-06-20';
DECLARE @month_start DATE = DATETRUNC(MONTH, @today);
SELECT @month_start AS month_start, DATEADD(MONTH, 1, @month_start) AS next_month_start,
       COUNT(*) AS orders_this_month
FROM dbo.orders
WHERE ordered_at >= @month_start AND ordered_at < DATEADD(MONTH, 1, @month_start);

month_start  next_month_start  orders_this_month
-----------  ----------------  -----------------
2026-06-01   2026-07-01        15

This query cuts the month in UTC. If the operations team is in Istanbul, you have to build the month boundary in Istanbul time first and then convert it to UTC. The difference for January 2026:

DECLARE @local_start DATETIME2(0) = '2026-01-01', @local_end DATETIME2(0) = '2026-02-01';
DECLARE @utc_start DATETIME2(0) = CAST(@local_start AT TIME ZONE 'Turkey Standard Time' AT TIME ZONE 'UTC' AS DATETIME2(0)),
        @utc_end   DATETIME2(0) = CAST(@local_end   AT TIME ZONE 'Turkey Standard Time' AT TIME ZONE 'UTC' AS DATETIME2(0));
SELECT @utc_start AS utc_start, @utc_end AS utc_end,
       (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= '2026-01-01' AND ordered_at < '2026-02-01') AS january_utc,
       (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= @utc_start AND ordered_at < @utc_end) AS january_istanbul;

utc_start            utc_end              january_utc  january_istanbul
-------------------  -------------------  -----------  ----------------
2025-12-31 21:00:00  2026-01-31 21:00:00  32           33

Istanbul's January starts at 21:00 on December 31 in UTC. Because we applied the conversion to the two boundary values and not to the column, the query stayed sargable; had we put ordered_at AT TIME ZONE ... in WHERE, it would be computed for every row.

For "the same week last year", DATEADD(YEAR, -1, ...) gives the wrong start: June 15, 2026 is a Monday, June 15, 2025 a Sunday. In a weekly comparison the weekdays should line up, so we go back 52 weeks (364 days):

DECLARE @today DATE = '2026-06-20';
DECLARE @week_start DATE = DATETRUNC(ISO_WEEK, @today);
SELECT @week_start AS week_start, DATEADD(WEEK, -52, @week_start) AS last_year_week_start,
       DATEPART(ISO_WEEK, DATEADD(WEEK, -52, @week_start)) AS last_year_iso_week,
       (SELECT COUNT(*) FROM dbo.orders
        WHERE ordered_at >= @week_start AND ordered_at < DATEADD(DAY, 7, @week_start)) AS this_week,
       (SELECT COUNT(*) FROM dbo.orders
        WHERE ordered_at >= DATEADD(WEEK, -52, @week_start) AND ordered_at < DATEADD(WEEK, -51, @week_start)) AS same_week_last_year;

week_start  last_year_week_start  last_year_iso_week  this_week  same_week_last_year
----------  --------------------  ------------------  ---------  -------------------
2026-06-15  2025-06-16            25                  5          6

Both weeks start on Monday and both are ISO week 25. Going back 52 weeks lands on the same ISO week in most years, but after an ISO year with 53 weeks it shifts by one week; in those years you have to compare by DATEPART(ISO_WEEK, ...) and the ISO year.

The last report is delivery time: hours from order to shipment and the number of orders shipped within 48 hours. DATEDIFF(HOUR, ...) counts hour boundaries, so we compute the average from minutes:

SELECT DATETRUNC(MONTH, ordered_at) AS order_month,
       COUNT(*) AS shipped_orders,
       CAST(AVG(DATEDIFF(MINUTE, ordered_at, shipped_at) / 60.0) AS DECIMAL(6,1)) AS avg_hours,
       MAX(DATEDIFF(HOUR, ordered_at, shipped_at)) AS max_hours,
       SUM(CASE WHEN shipped_at < DATEADD(HOUR, 48, ordered_at) THEN 1 ELSE 0 END) AS within_48h
FROM dbo.orders
WHERE shipped_at IS NOT NULL AND ordered_at >= '2026-01-01' AND ordered_at < '2026-07-01'
GROUP BY DATETRUNC(MONTH, ordered_at)
ORDER BY order_month;

order_month          shipped_orders  avg_hours  max_hours  within_48h
-------------------  --------------  ---------  ---------  ----------
2026-01-01 00:00:00  32              50.7       79         13
2026-02-01 00:00:00  16              48.3       80         8
2026-03-01 00:00:00  18              52.7       79         7
2026-04-01 00:00:00  17              52.5       79         6
2026-05-01 00:00:00  11              53.5       79         4
2026-06-01 00:00:00  12              50.3       80         4

I wrote the 48-hour condition as shipped_at < DATEADD(HOUR, 48, ordered_at) instead of DATEDIFF(HOUR, ...) <= 48; the latter would also count 48 hours and 59 minutes as 48.

Function Summary and PostgreSQL Counterparts

I ran the expressions in the PostgreSQL column in my own PostgreSQL 18 container:

T-SQL	NULL and edge behaviour	PostgreSQL
`SYSUTCDATETIME()`	`DATETIME2(7)`, UTC	`now()` (`timestamptz`), `localtimestamp`
`DATEADD(MONTH, n, d)`	Clips to month end; `NULL` → `NULL`	`d + interval '1 month'`
`DATEDIFF(part, a, b)`	Counts boundaries, `INT`; week always Sunday	`b - a` (days), `age`, `extract(epoch FROM ...)`
`EOMONTH(d [, n])`	Returns `DATE`	`date_trunc('month', d) + interval '1 month - 1 day'`
`DATEFROMPARTS(y, m, d)`	`NULL` part → `NULL`; Msg 289 when invalid	`make_date`, error when invalid
`DATETRUNC(part, d)`	2022+; `WEEK` follows `DATEFIRST`	`date_trunc`, ISO weeks
`DATEPART(ISO_WEEK, d)`	Independent of language setting	`extract(week FROM d)`
`x AT TIME ZONE 'Turkey Standard Time'`	Windows names; adds an offset to input without one	`AT TIME ZONE 'Europe/Istanbul'`, IANA names

In PostgreSQL the meaning of AT TIME ZONE also flips with the input type: timestamptz '2025-12-31 23:06:27+00' AT TIME ZONE 'Europe/Istanbul' returns local time without an offset (2026-01-01 02:06:27), while a timestamp without an offset is taken to be in that zone and converted to timestamptz.

In Production

Store UTC, display local time. Define columns as DATETIME2 and fill them with SYSUTCDATETIME(); if you need to store the offset itself (for example when the customer's local time of the order matters to the business), use DATETIMEOFFSET. In reports, apply the time zone conversion to the range boundaries, not to the column; that way the query keeps using the index.

Session settings change results. SET LANGUAGE and SET DATEFIRST come from the login's default language or the connection string, and they affect DATETIME literals, DATENAME and DATEPART(WEEKDAY, ...). Send dates from the application as typed parameters, not strings; EF Core and Dapper already do this with DateTime parameters. If you write date literals in scripts, use either the unseparated '20260403' or the ISO form with the T separator.

In containers the time zone is usually UTC, while on Windows servers it is that of the installation site. Code written with GETDATE() and SYSDATETIME() silently produces different results when the database moves to another region. This is one of the common reasons a query that works in the lab gives a different result in production.

Common Pitfalls

The hyphenated '2026-04-03' literal being read as April 3 or March 4 in DATETIME, depending on the language setting.
Writing month end as BETWEEN ... AND '... 23:59:59.997'; in DATETIME .999 rounds to the next day, in DATETIME2 rows after .997 get lost.
Computing age with DATEDIFF(YEAR, ...); assuming DATEDIFF(WEEK, ...) follows DATEFIRST.
Using DATEDIFF for millisecond differences and getting Msg 535; use DATEDIFF_BIG.
Converting a UTC value in one step with AT TIME ZONE 'Turkey Standard Time'; without AT TIME ZONE 'UTC' first, the clock time does not change.
Writing weekend or weekday checks with DATEPART(WEEKDAY, ...) and DATENAME, tied to session settings.
Making the index unusable with WHERE YEAR(ordered_at) = 2026 or WHERE ordered_at AT TIME ZONE ....

A Small Experiment

★☆☆ Using customers.created_at, compute how many days each customer has been registered, taking 2026-06-30 as "today". Do DATEDIFF(DAY, ...) and DATEDIFF(HOUR, ...) / 24 give the same result? If not, why?

★★☆ Compute the share of orders placed at the weekend so that the result does not depend on the session's language and DATEFIRST setting. Run the query under SET LANGUAGE Turkish and SET LANGUAGE us_english and show that the results stay the same.

★★★ Write a report that gives the daily order count in Istanbul time: generate the day boundaries with GENERATE_SERIES, convert each day's start and end to UTC and count orders with a half-open range. Compare December 31, 2025 and January 1, 2026 with a report grouped by UTC.

In the next post we move on to conversion and formatting functions: CONVERT style codes, TRY_CONVERT and TRY_PARSE, the cost of FORMAT, and the details of ISNULL, COALESCE, NULLIF, IIF and CHOOSE.