SQL Date Functions
Almost every real database tracks when something happened: an order date, a hire date, a signup timestamp. SQL date functions let you extract parts of a date (year, month, day), do date arithmetic (add 30 days, find the difference between two dates), and format dates for display or comparison. Getting comfortable with them is essential, because dates are one of the most common sources of subtle, silent bugs in SQL code.
Overview: How SQL Engines Handle Dates
Unlike numbers or text, “date” is not one universal storage format across database systems. SQLite (the engine used for the examples on this page) has no dedicated date/time storage class at all. Instead, it lets you store dates as:
- TEXT in ISO 8601 format, e.g.
'2024-05-01'or'2024-05-01 14:30:00'— the recommended approach, because ISO text sorts correctly as plain strings. - REAL as a Julian day number — the number of days since noon on November 24, 4714 BC (proleptic Julian calendar).
- INTEGER as Unix time — seconds since 1970-01-01 00:00:00 UTC.
SQLite’s date functions (date(), time(), datetime(), julianday(), strftime()) all work by parsing whichever of these formats you give them, internally converting to a Julian day number, doing the requested calculation, and formatting the result back out. This is why you can freely mix a text date and a Julian-day calculation in the same query — SQLite normalizes everything through Julian day arithmetic behind the scenes.
Other engines take a stricter approach: MySQL and PostgreSQL have native DATE, DATETIME/TIMESTAMP column types with fixed internal binary representations, and SQL Server has DATE, DATETIME2, and related types. The function names differ between engines even though the underlying ideas — extract a part, add an interval, compute a difference — are the same everywhere.
Syntax
The general form of SQLite’s date functions is:
DATE(timestring, modifier, modifier, ...)
TIME(timestring, modifier, modifier, ...)
DATETIME(timestring, modifier, modifier, ...)
JULIANDAY(timestring, modifier, modifier, ...)
STRFTIME(format, timestring, modifier, modifier, ...)| Function | Returns | Typical use |
|---|---|---|
DATE(...) |
Text, YYYY-MM-DD |
Normalize or shift a date, dropping the time part |
TIME(...) |
Text, HH:MM:SS |
Extract or shift just the time part |
DATETIME(...) |
Text, YYYY-MM-DD HH:MM:SS |
Normalize or shift a full timestamp |
JULIANDAY(...) |
Real number | Precise arithmetic, especially date differences |
STRFTIME(format, ...) |
Text, custom format | Extract a specific part (year, month, weekday, etc.) |
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP |
Text | The current date/time in UTC |
The optional modifiers are applied left to right and let you shift a date: '+N days', '-N months', '+N years', 'start of month', 'start of year', 'start of day', and 'weekday N' are the most common. strftime() format codes include %Y (4-digit year), %m (2-digit month), %d (2-digit day), %H/%M/%S (time), and %j (day of year, 001–366).
Equivalent functions in other dialects
| Task | SQLite | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|---|
| Current date | CURRENT_DATE |
CURDATE() |
CURRENT_DATE |
GETDATE() |
| Add days | DATE(d,'+7 days') |
DATE_ADD(d, INTERVAL 7 DAY) |
d + INTERVAL '7 days' |
DATEADD(day,7,d) |
| Extract year | strftime('%Y', d) |
YEAR(d) |
EXTRACT(YEAR FROM d) |
YEAR(d) |
| Difference in days | julianday(d2)-julianday(d1) |
DATEDIFF(d2, d1) |
d2 - d1 |
DATEDIFF(day,d1,d2) |
Examples
Example 1: Extracting parts of a date
CREATE TABLE events (
event_name TEXT,
event_date TEXT
);
INSERT INTO events (event_name, event_date) VALUES
('Kickoff Meeting', '2024-01-15'),
('Product Launch', '2024-03-22'),
('Mid-Year Review', '2024-07-01'),
('Year-End Party', '2024-12-20');
SELECT event_name, event_date,
strftime('%Y', event_date) AS year,
strftime('%m', event_date) AS month,
strftime('%j', event_date) AS day_of_year
FROM events
ORDER BY event_date;Result:
| event_name | event_date | year | month | day_of_year |
|---|---|---|---|---|
| Kickoff Meeting | 2024-01-15 | 2024 | 01 | 015 |
| Product Launch | 2024-03-22 | 2024 | 03 | 082 |
| Mid-Year Review | 2024-07-01 | 2024 | 07 | 183 |
| Year-End Party | 2024-12-20 | 2024 | 12 | 355 |
strftime() reads the stored ISO text, parses it into a Julian day internally, and re-formats it according to the codes you supply. Note that every value it returns is TEXT, even year — that detail matters, as you’ll see in Common Mistakes below.
Example 2: Date arithmetic with JULIANDAY
CREATE TABLE order_shipping (
order_id INTEGER,
order_date TEXT,
ship_date TEXT
);
INSERT INTO order_shipping (order_id, order_date, ship_date) VALUES
(1, '2024-05-01', '2024-05-04'),
(2, '2024-05-10', '2024-05-10'),
(3, '2024-05-15', '2024-05-20');
SELECT order_id, order_date, ship_date,
julianday(ship_date) - julianday(order_date) AS days_to_ship
FROM order_shipping
ORDER BY order_id;Result:
| order_id | order_date | ship_date | days_to_ship |
|---|---|---|---|
| 1 | 2024-05-01 | 2024-05-04 | 3.0 |
| 2 | 2024-05-10 | 2024-05-10 | 0.0 |
| 3 | 2024-05-15 | 2024-05-20 | 5.0 |
julianday() converts each date to a floating-point Julian day number, so subtracting two of them gives an exact number of days (including fractional days if a time-of-day is present). This is far safer than trying to subtract date strings directly.
Example 3: Shifting dates with modifiers
SELECT DATE('2026-07-20', '+30 days') AS due_date,
DATE('2026-07-20', 'start of month') AS month_start,
DATE('2026-07-20', 'start of month', '+1 month', '-1 day') AS month_end;Result:
| due_date | month_start | month_end |
|---|---|---|
| 2026-08-19 | 2026-07-01 | 2026-07-31 |
Modifiers apply in sequence, left to right. 'start of month' snaps the date to the 1st, then '+1 month' moves to the 1st of the following month, and '-1 day' steps back one day — a common trick for finding the last day of a month regardless of how many days it has.
How It Works Step by Step (Under the Hood)
When SQLite evaluates something like strftime('%Y', order_date) inside a query, it performs these steps in order:
- Parse: the input string (or number) is matched against SQLite’s recognized date/time formats (
YYYY-MM-DD,YYYY-MM-DD HH:MM:SS, Julian day, Unix epoch, etc.). - Normalize: the parsed value is converted internally into a Julian day number (a floating-point value), which is the common currency for all date math.
- Apply modifiers: each modifier argument (
'+7 days','start of month', …) adjusts that Julian day number, applied strictly in the order given. - Format: the final Julian day is converted back into whatever output the function promises — a full timestamp for
datetime(), just the date fordate(), or a custom string forstrftime().
This matters for query planning too: because date functions are ordinary scalar functions, SQLite cannot use an index to skip evaluating them for every row unless the expression matches an indexed expression exactly. A condition like WHERE strftime('%Y', order_date) = '2024' forces a full scan and a per-row function call, whereas a range condition like WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' can use a plain index on order_date, because it compares the stored text directly instead of transforming every row first. In query-processing order, this filter is applied during the WHERE stage — after FROM builds the row set, before GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT run.
Common Mistakes
Mistake 1: Mixing date formats breaks sorting and filtering
CREATE TABLE signups (
user_name TEXT,
signup_date TEXT
);
INSERT INTO signups (user_name, signup_date) VALUES
('Alice', '2024-01-05'),
('Bob', '01/20/2024'),
('Carla', '2024-02-10');
-- Wrong: signup_date is stored in two different formats
SELECT user_name, signup_date
FROM signups
ORDER BY signup_date;Result (wrong order): Bob (01/20/2024), Alice (2024-01-05), Carla (2024-02-10). Because ORDER BY on text compares characters left to right, '01/20/2024' sorts before '2024-01-05' simply because the character '0' comes before '2' — not because it’s chronologically earlier. The dates aren’t actually being compared as dates at all.
CREATE TABLE signups_fixed (
user_name TEXT,
signup_date TEXT
);
INSERT INTO signups_fixed (user_name, signup_date) VALUES
('Alice', '2024-01-05'),
('Bob', '2024-01-20'),
('Carla', '2024-02-10');
SELECT user_name, signup_date
FROM signups_fixed
ORDER BY signup_date;Result (correct order): Alice (2024-01-05), Bob (2024-01-20), Carla (2024-02-10). Once every row uses the same ISO 8601 format, plain string sorting is also correct chronological sorting.
Mistake 2: Comparing a text result to a number
CREATE TABLE payments (
payment_id INTEGER,
payment_date TEXT
);
INSERT INTO payments (payment_id, payment_date) VALUES
(1, '2023-11-05'),
(2, '2024-02-14'),
(3, '2024-06-30');
-- Wrong: strftime() returns TEXT, but 2024 here is an INTEGER literal
SELECT * FROM payments WHERE strftime('%Y', payment_date) = 2024;Result: 0 rows. This is surprising but consistent with SQLite’s type rules: when neither side of a comparison has column affinity (a function result and a bare integer literal both lack it), SQLite compares by storage class, and every INTEGER value is defined to sort below every TEXT value regardless of what the text says. '2024' (text) is never equal to 2024 (integer), even though they look the same.
CREATE TABLE payments (
payment_id INTEGER,
payment_date TEXT
);
INSERT INTO payments (payment_id, payment_date) VALUES
(1, '2023-11-05'),
(2, '2024-02-14'),
(3, '2024-06-30');
-- Correct: compare text to text
SELECT * FROM payments WHERE strftime('%Y', payment_date) = '2024';Result: payment_id 2 (2024-02-14) and payment_id 3 (2024-06-30). Quoting the year as a string fixes the comparison.
Best Practices
- Always store dates as ISO 8601 text (
YYYY-MM-DDorYYYY-MM-DD HH:MM:SS) so lexicographic sorting matches chronological order. - Use
julianday()for date differences instead of subtracting strings or relying on manual day-counting. - Remember that
strftime()always returns TEXT — compare its result to a quoted string, never a bare number. - Prefer range comparisons (
date_col >= '2024-01-01' AND date_col < '2025-01-01') over wrapping the column in a function, since range comparisons on the raw column can use an index. - Store timestamps in UTC and convert to local time only for display, since SQLite has no native timezone type.
- Use
date()/datetime()to normalize input before comparing values that might carry unexpected precision (extra time components, etc.).
Practice Exercises
- Using the
eventstable from Example 1, write a query that returns only events that happened in July (any year). Hint: filter onstrftime('%m', event_date). - Using the
order_shippingtable from Example 2, write a query that returns only orders wheredays_to_shipwas greater than 2. - Write a single expression using
DATE()and modifiers that computes the last day of the previous month relative to'2026-07-20'. Expected result:2026-06-30.
Summary
- SQL date functions let you extract parts of a date, shift dates forward or backward, and compute precise differences between dates.
- SQLite stores dates as TEXT, REAL (Julian day), or INTEGER (Unix time) and normalizes everything to Julian day internally when you call a date function.
- Key functions:
date(),time(),datetime(),julianday(), andstrftime(), plus modifiers like'+N days'and'start of month'. - Store dates as ISO 8601 text so string sorting equals chronological sorting.
- Never compare a
strftime()result to an unquoted number — it always returns TEXT. - Function names for the same task differ across MySQL, PostgreSQL, and SQL Server, even though the underlying logic is the same.
