SQL Date Functions Explained: Adding, Comparing and Grouping Dates Without Bugs

A daily sales report shows almost nothing for 10 November, even though the shop was busy all day. The query looked correct: WHERE created_at BETWEEN '2026-11-10' AND '2026-11-10'. It only matched orders placed at exactly midnight. Date logic is full of traps like this, and they rarely cause errors; they just produce wrong numbers.

We ran the examples in SQLite 3.50, which stores dates as text and uses functions like date() and strftime(). The outputs are from that run. Equivalents for PostgreSQL, MySQL and SQL Server are listed from their documentation; we did not run those.

Date and time types

Use the database’s real date types where available, not plain text. SQLite has no dedicated date type, so it relies on ISO 8601 text, YYYY-MM-DD HH:MM:SS, which sorts correctly as text. In SQLite, date('2026-11-10 14:35:00'), time(...) and datetime(...) returned 2026-11-10, 14:35:00 and the full timestamp.

Current date and time

Every database has a way to get “now”, but they differ: CURRENT_DATE and CURRENT_TIMESTAMP are standard and widely supported; MySQL adds NOW() and CURDATE(); SQL Server uses GETDATE() or SYSDATETIME(). Know whether “now” is in UTC or the server’s local time zone, because that affects every “today” query.

Adding and subtracting time

SELECT date('2026-11-10', '+30 days');   -- 2026-12-10
SELECT date('2026-11-10', 'start of month');                         -- 2026-11-01
SELECT date('2026-11-10', 'start of month', '+1 month', '-1 day');   -- 2026-11-30

SQLite uses modifiers; the last example finds the last day of the month by going to the first of the next month and stepping back a day, a trick that works in every database. The same “add 30 days” looks like this elsewhere:

-- PostgreSQL:  d + INTERVAL '30 days'
-- MySQL:       DATE_ADD(d, INTERVAL 30 DAY)
-- SQL Server:  DATEADD(DAY, 30, d)

Month-end and leap-year surprises

SELECT date('2026-01-31', '+1 month');   -- 2026-03-03
SELECT date('2028-02-29', '+1 year');    -- 2029-03-01

“31 February” does not exist, so SQLite rolled the extra days into March. Other databases clamp to the last valid day instead: PostgreSQL, MySQL and SQL Server document that adding a month to 31 January gives 28 or 29 February. Neither is wrong, but they disagree, so a billing job that “adds one month” can charge on different days depending on the database. For subscriptions, store the intended day of the month and calculate each date from it, rather than adding a month to the previous date repeatedly.

Differences between dates

SELECT julianday('2026-12-25') - julianday('2026-11-10');   -- 45.0

Forty-five days to 25 December. SQLite converts both dates to Julian day numbers and subtracts. Other dialects:

-- PostgreSQL:  DATE '2026-12-25' - DATE '2026-11-10'   (an integer)
-- MySQL:       DATEDIFF('2026-12-25', '2026-11-10')    (later date first)
-- SQL Server:  DATEDIFF(DAY, '2026-11-10', '2026-12-25') (unit first, earlier date first)

Watch the argument order: MySQL and SQL Server both have DATEDIFF but put the dates in opposite orders, so a query copied between them returns a negative number. SQL Server’s DATEDIFF also counts boundaries crossed, not full periods: the difference in years between 31 December and 1 January is 1.

Extracting parts of a date

SELECT strftime('%w', '2026-11-10'),   -- 2   (day of week, 0 = Sunday)
       strftime('%Y-%m', '2026-11-10'), -- 2026-11
       strftime('%j', '2026-11-10');    -- 314 (day of year)

10 November 2026 is a Tuesday, day 2 when Sunday is 0. Be careful here too: day-of-week numbering differs by database and settings, with some starting the week on Monday. The standard EXTRACT(YEAR FROM d) works in PostgreSQL and MySQL; MySQL and SQL Server also offer YEAR(d), MONTH(d) and DAY(d).

The BETWEEN trap

We stored six orders, including some just before and after 10 November:

id 1  2026-11-09 23:30:00
id 2  2026-11-10 00:15:00
id 3  2026-11-10 18:00:00
id 4  2026-11-10            (date only)
id 5  2026-11-11 00:00:00
id 6  10/11/2026            (wrong format)

WHERE created_at BETWEEN '2026-11-10' AND '2026-11-10'          -- [4]
WHERE created_at >= '2026-11-10' AND created_at < '2026-11-11'  -- [2, 3, 4]

BETWEEN matched only order 4, stored without a time. Orders 2 and 3 have times after midnight, so they are greater than '2026-11-10' and fall outside the range. The half-open range, from the start of the day up to but not including the next day, matched all three orders on 10 November and correctly excluded order 5 at midnight on the 11th. This pattern works for any period, such as a month (>= '2026-11-01' AND < '2026-12-01'), and never needs to know how many days a month has.

Grouping by day and month

SELECT date(created_at) AS d, SUM(total)
FROM orders
WHERE date(created_at) IS NOT NULL
GROUP BY d ORDER BY d;

-- 2026-11-09  100
-- 2026-11-10  550
-- 2026-11-11  400

Truncating a timestamp to its day or month is the basis of most reports. PostgreSQL uses DATE_TRUNC('month', d); SQL Server 2022 added DATETRUNC; MySQL has no direct equivalent, so people use DATE_FORMAT(d, '%Y-%m-01'). Remember that days with no orders do not appear at all. For charts, join to a calendar table or generated date series so empty days show as zero. See our GROUP BY guide for more on aggregation.

Dates and indexes

EXPLAIN QUERY PLAN ... WHERE date(created_at) = '2026-11-10'
-- SCAN orders USING COVERING INDEX ix

EXPLAIN QUERY PLAN ... WHERE created_at >= '2026-11-10' AND created_at < '2026-11-11'
-- SEARCH orders USING COVERING INDEX ix (created_at>? AND created_at<?)

Both return the same rows, but wrapping the column in date() forced a scan of the whole index, while the range let the database jump straight to the right section. On a large table, that is the difference between milliseconds and seconds. Keep the column bare on one side of the comparison, and apply functions to the constants instead. Our SQL indexes guide explains why.

Badly formatted dates

Order 6 was stored as 10/11/2026. SQLite’s date() returned NULL for it, so it silently disappeared from both the day filter and the report. Is it 10 November or 11 October? Nobody can tell. Store dates in a real date column or in ISO format, validate input at the boundary, and add a CHECK constraint where the database allows, so bad values are rejected instead of quietly ignored.

Time zones

An order at 23:30 in India on 9 November is 18:00 UTC the same day, but an order at 02:00 IST on 10 November is 20:30 UTC on 9 November. Which day it “belongs to” depends on whose day you mean. The common rule is to store timestamps in UTC, or in a time-zone-aware type, and convert to the user’s or business’s local time only when reporting. In SQLite we shifted a UTC timestamp by hand: datetime('2026-11-10 12:00:00', '+5 hours', '+30 minutes') gave 17:30. PostgreSQL’s AT TIME ZONE and MySQL’s CONVERT_TZ do this properly, including daylight saving rules.

Dialect cheat sheet

Final thoughts

Date bugs hide because the queries run without errors. Use real date types, store times in UTC, filter with half-open ranges instead of BETWEEN, keep indexed columns free of functions, and test the month-end, leap-year and midnight boundaries. When moving SQL between databases, rewrite date logic deliberately rather than assuming it carries over.

Frequently asked questions

Keep learning