.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 2 October 2026
Most date bugs in SQL Server don't raise an error. The query runs, rows come back, the number at the bottom of the report is off by a month, and nobody catches it until someone reconciles that report against an invoice. A broken join gets found in seconds. A DATEDIFF counting calendar boundaries when you wanted elapsed time can sit in a monthly summary for a year.
The second half of the trouble is version drift. DATETRUNC and DATE_BUCKET arrived in SQL Server 2022, DATEDIFF_BIG and AT TIME ZONE in 2016, EOMONTH back in 2012. Write a query on a 2022 box, deploy it to the 2019 instance nobody upgraded, and you get 'DATETRUNC' is not a recognized built-in function name.
Checking the SQL Server version takes one query and settles that argument before anyone writes anything. I picked the habit up after assuming an instance was 2019 for about three months. It had been 2022 since an upgrade nobody wrote down, and the build number was the only thing on that box telling the truth.
Every example here runs against the same three tables, which the sibling articles in this series reuse so the queries stay comparable.
CREATE TABLE dbo.customers (
customer_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
company_name nvarchar(200) NOT NULL,
contact_email varchar(255) NULL,
country_code char(2) NOT NULL,
created_utc datetime2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);
CREATE TABLE dbo.orders (
order_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
customer_id int NOT NULL REFERENCES dbo.customers (customer_id),
order_date datetime2(3) NOT NULL,
shipped_date datetime2(3) NULL,
status varchar(20) NOT NULL,
total_amount decimal(12,2) NOT NULL
);
CREATE TABLE dbo.order_items (
order_item_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
order_id int NOT NULL REFERENCES dbo.orders (order_id),
sku varchar(40) NOT NULL,
quantity int NOT NULL,
unit_price decimal(12,2) NOT NULL
);Date and time data types in SQL Server
Six types, and four of them belong in a table you're creating today. date is three bytes, spans 0001-01-01 to 9999-12-31 and carries no time component. time(n) is a time of day on its own. datetime2(n) holds both with a precision you pick, 0 to 7 fractional second digits, and datetimeoffset(n) is datetime2 with a UTC offset stapled on.
The two you inherit are datetime and smalldatetime. smalldatetime rounds to the nearest minute and starts in 1900, which makes it useless for anything with a timestamp on it. datetime is the one you'll meet in every schema written before about 2010: eight fixed bytes, a range that begins at 1753-01-01, and fractional seconds stored only in increments of .000, .003 and .007.
So I use datetime2(3) for new columns. A datetime column advertises milliseconds and then rounds every value it stores to the nearest .000, .003 or .007, while datetime2(3) hands back the millisecond you gave it. datetime2 storage runs 6 bytes for precision 0 to 2, 7 bytes for 3 and 4, and 8 bytes for 5 to 7, so datetime2(3) is a byte smaller than datetime and honest about what it holds. It also sits above datetime in the data type precedence list, so mixing the two in one expression converts the old type upward, which is the direction you want.
The 1753-01-01 floor on datetime gets explained as a calendar-reform artefact, Britain having dropped eleven days out of September 1752 and the Sybase engineers deciding they would not try to model that. Every version of that story I have read is secondhand, mine included, so treat it as folklore that happens to be widely repeated. The floor is 1753-01-01 either way.
Migrating an existing column means an ALTER TABLE ... ALTER COLUMN, and altering a column in SQL Server is not free on a table with any size to it. Indexes and constraints on the column block the statement outright, statistics you created by hand have to come off first, and the rewrite holds a schema modification lock while it runs. Budget for it.
SQL Server date functions for the current time: GETDATE and SYSDATETIME
Five functions return "now". They differ in return type and in precision.
SELECT GETDATE() AS getdate_val, -- datetime
GETUTCDATE() AS getutcdate_val, -- datetime, UTC
SYSDATETIME() AS sysdatetime_val, -- datetime2(7)
SYSUTCDATETIME() AS sysutcdatetime_val, -- datetime2(7), UTC
SYSDATETIMEOFFSET() AS sysdatetimeoffset_val;GETDATE and GETUTCDATE are the old pair and they return datetime, so everything they hand you is snapped to that .000/.003/.007 grid. The SYS* family landed in SQL Server 2008 and returns datetime2(7), accurate to 100 nanoseconds, which is what you want for anything you'll later subtract. SYSDATETIMEOFFSET adds the server's offset from UTC.
The .000, .003 and .007 rounding on datetime
Microsoft documents this in a table on the datetime reference page. Run it yourself:
SELECT CAST('2026-08-26 23:59:59.990' AS datetime) AS a,
CAST('2026-08-26 23:59:59.994' AS datetime) AS b,
CAST('2026-08-26 23:59:59.998' AS datetime) AS c,
CAST('2026-08-26 23:59:59.999' AS datetime) AS d;You get 23:59:59.990, 23:59:59.993, 23:59:59.997 and then, for the last one, 2026-08-27 00:00:00.000. A value on 26 August became a value on 27 August. Bucket events by day off a datetime column and that row counts against the wrong day.
Storing UTC
I store UTC in datetime2(3) and convert at the presentation edge, which is why created_utc in the schema above defaults to SYSUTCDATETIME(). The alternative, datetimeoffset, keeps the original offset, and that's worth the extra bytes when the offset itself is business data (a booking made at 09:00 local time in Warsaw is a different fact from the same instant in UTC). For everything else, one clock across the whole database is simpler to reason about, and the conversion happens once, at the edge where somebody is reading a screen.
DATEADD: adding and subtracting intervals
DATEADD in SQL Server takes three arguments, DATEADD(datepart, number, date). The return type matches the type of the date you pass in. A negative number subtracts.
SELECT order_id,
order_date,
DATEADD(day, 30, order_date) AS payment_due,
DATEADD(hour, -4, order_date) AS four_hours_before,
DATEADD(month, 1, order_date) AS next_month,
DATEADD(year, -1, order_date) AS same_day_last_year
FROM dbo.orders
WHERE order_id = 4821;The dateparts it accepts are year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond and nanosecond. Each of them has abbreviations as well, so mm stands in for month and dd for day, though the full words read better in a script somebody else has to maintain. day, dayofyear and weekday all add plain days. week adds seven of them.
Two behaviours to keep in mind. A fractional number is truncated, so DATEADD(day, 1.9, @d) adds one day. Month arithmetic clamps to the end of the target month, so DATEADD(month, 1, '2026-01-31') gives 2026-02-28. A subscription that renews on the 31st therefore drifts to the 28th and stays there once you start adding to each result in turn. Add to the original anchor date every time and the 31st comes back.
DATEDIFF in SQL Server counts boundaries crossed
Most of the date bugs I have chased come back to a misreading of this function. Microsoft's wording is precise and easy to skim past: DATEDIFF "returns the count (as a signed integer value) of the specified datepart boundaries crossed" between two dates. It counts how many times the calendar ticked over between the two values. It does not measure the time that passed. The two answers agree often enough that the gap between them only shows up when it costs you something.
SELECT DATEDIFF(year, '2025-12-31 23:59:59', '2026-01-01 00:00:00') AS one_second_apart;That returns 1. One second of elapsed time, one year of difference, because exactly one year boundary sat between the two values. The same logic gives you DATEDIFF(month, '2026-01-31', '2026-02-01') = 1 for a single day.
Age is where it bites hardest, because an age is a date difference anyone can check in their head. A customer born on 1990-12-01, evaluated on 2026-08-26, is 35 years old. DATEDIFF(year, '1990-12-01', '2026-08-26') returns 36, because 36 New Year's Days have passed. Subtract a year when the anniversary hasn't come round yet:
DECLARE @dob date = '1990-12-01';
DECLARE @asof date = '2026-08-26';
SELECT DATEDIFF(year, @dob, @asof)
- CASE WHEN DATEADD(year, DATEDIFF(year, @dob, @asof), @dob) > @asof
THEN 1 ELSE 0 END AS age_in_years;It's ugly and it's correct. Wrapping it in a scalar function costs you a call per row, so I usually inline it instead.
DATEDIFF_BIG and the overflow limits
The int return type is a hard ceiling. DATEDIFF with second tops out at roughly 68 years of span, with millisecond at about 24 days, and with microsecond or nanosecond you're into minutes and seconds of range. Cross the line and you get The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.
-- Overflows: 1900 to 2026 in seconds exceeds an int
SELECT DATEDIFF(second, '1900-01-01', SYSUTCDATETIME());
-- Works: bigint return type, SQL Server 2016 and later
SELECT DATEDIFF_BIG(second, '1900-01-01', SYSUTCDATETIME());DATEDIFF_BIG takes the same arguments and returns bigint. It shipped in SQL Server 2016, so it's available on everything still in support, and there's no reason to reach for the int version when you're diffing epochs.
DATEPART and DATENAME
DATEPART in SQL Server returns an int. DATENAME returns nvarchar and is language dependent. That difference decides which of the two is safe to put in a WHERE clause.
SELECT DATEPART(year, order_date) AS yr,
DATEPART(quarter, order_date) AS qtr,
DATEPART(month, order_date) AS mth,
DATENAME(month, order_date) AS month_name,
DATENAME(weekday, order_date) AS day_name
FROM dbo.orders;Now change the session language and run it again:
SET LANGUAGE French;
SELECT DATENAME(month, '2026-08-26'); -- août
SET LANGUAGE us_english;
SELECT DATENAME(month, '2026-08-26'); -- AugustSo a filter like WHERE DATENAME(weekday, order_date) = 'Saturday' stops matching the moment a login with a different default language runs it. The grid comes back empty and nothing is raised to tell you why. Use DATENAME for labels on a screen and DATEPART for every decision the query makes.
SET DATEFIRST, week and iso_week
DATEPART has its own language problem, in the week and weekday dateparts. Both depend on SET DATEFIRST, and the default for DATEFIRST comes from the login's language: 7 (Sunday) for us_english, 1 (Monday) for British English. Two logins, one query, different answers.
SET DATEFIRST 7; -- Sunday is day 1
SELECT DATEPART(weekday, '2026-08-26') AS us_style; -- 4
SET DATEFIRST 1; -- Monday is day 1
SELECT DATEPART(weekday, '2026-08-26') AS iso_style; -- 326 August 2026 is a Wednesday, and it's day 4 or day 3 depending on a session setting you probably didn't set. The stable answer is iso_week, which follows ISO 8601: weeks start on Monday and week 1 is the week containing the first Thursday of the year. It ignores DATEFIRST entirely.
SELECT DATEPART(iso_week, '2027-01-01') AS iso_wk, -- 53
DATEPART(year, '2027-01-01') AS cal_year; -- 20271 January 2027 falls in ISO week 53 of 2026. Note that DATEPART(year, ...) still says 2027, so grouping by year and iso_week together splits that week in two. If you report on ISO weeks you need an ISO year expression as well, and that's the point where a small calendar table starts paying for itself.
EOMONTH, DATETRUNC and DATE_BUCKET
EOMONTH has been in the product since SQL Server 2012. It returns the last day of the month containing the date you give it. The optional second argument shifts the result by a whole number of months, forward on a positive value and backward on a negative one, so the previous month's closing boundary needs no arithmetic of its own.
SELECT EOMONTH('2026-02-10') AS this_month_end, -- 2026-02-28
EOMONTH('2026-02-10', -1) AS prev_month_end, -- 2026-01-31
EOMONTH('2028-02-10') AS leap_year_end; -- 2028-02-29Leap years are handled, as the third line shows.
DATETRUNC arrived in SQL Server 2022 and deleted a pile of nested arithmetic from my scripts. It truncates a date down to the start of a datepart and returns the same type it was given.
SELECT DATETRUNC(year, order_date) AS year_start,
DATETRUNC(quarter, order_date) AS quarter_start,
DATETRUNC(month, order_date) AS month_start,
DATETRUNC(iso_week, order_date) AS iso_week_start,
DATETRUNC(day, order_date) AS midnight,
DATETRUNC(hour, order_date) AS hour_start
FROM dbo.orders;It handles everything from year down to microsecond. The three it refuses are weekday, tzoffset and nanosecond. Grouping monthly revenue is now a one-liner:
SELECT DATETRUNC(month, order_date) AS month_start,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM dbo.orders
WHERE status = 'shipped'
GROUP BY DATETRUNC(month, order_date)
ORDER BY month_start;DATE_BUCKET came in the same release and answers a different question: which fixed-width window does this timestamp fall into? Four-hour buckets, or ten-minute ones, out of a single call.
SELECT DATE_BUCKET(hour, 4, order_date) AS bucket_start,
COUNT(*) AS orders_in_bucket
FROM dbo.orders
WHERE order_date >= '20260801'
AND order_date < '20260901'
GROUP BY DATE_BUCKET(hour, 4, order_date)
ORDER BY bucket_start;The default origin is 1900-01-01 00:00:00.000, which happens to be a Monday, so week buckets align to Mondays without you doing anything. A fourth argument moves that origin to a fiscal year start or a shift boundary at 06:00. The number argument must be positive, the origin and the date have to be the same type, and the datepart has to be a literal, so a variable will not do. I have moved the origin exactly once, for a client whose shifts didn't line up with the calendar day, and I had to reopen the reference page to write this paragraph.
Truncating to a month or hour before SQL Server 2022
On 2019 and earlier the idiom is a DATEDIFF and DATEADD pair anchored to the integer zero, which SQL Server reads as 1900-01-01.
-- Start of month
SELECT DATEADD(month, DATEDIFF(month, 0, order_date), 0) AS month_start
FROM dbo.orders;
-- Start of hour
SELECT DATEADD(hour, DATEDIFF(hour, 0, order_date), 0) AS hour_start
FROM dbo.orders;Count the boundaries from 1900 to the value, then add that many back to 1900. It works down to hour and no lower, because DATEDIFF(second, 0, ...) against a 2026 date blows past the int ceiling I mentioned a few sections back. Anchor to a recent date if you need second-level buckets on an older instance, or swap the anchor for DATEDIFF_BIG. The return type here is datetime, so cast it if the surrounding expression cares about precision.
