Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 125 ยท Functions and Expressions

Date and Time Functions

Store, filter, format and calculate dates in SQL with SQLite's date(), time(), datetime(), strftime(), julianday() and modifiers, plus the PostgreSQL, MySQL and SQL Server versions.

Dates appear in almost every real query: orders from last month, employees hired before 2020, invoices due in 30 days, sales by weekday. Date handling is also where SQL dialects differ the most, so it pays to understand the ideas first and the function names second.

SQLite takes an unusual but simple approach: it has no separate date type. Dates and times are stored as ordinary text in the ISO 8601 format ('2025-03-15' or '2025-03-15 14:30:00'), and a small family of functions reads, calculates and reformats that text. In this lesson you'll learn those functions, the modifiers that do date arithmetic, how to filter date ranges correctly, and how the same jobs look in PostgreSQL, MySQL and SQL Server.

The shop database stores order times like '2025-01-26 13:35' in orders.ordered_at, and the company database stores dates like '2016-03-01' in employees.hire_date. You'll use both.

The date and time functions

SQLite has six functions. Each takes a time value followed by optional modifiers:

Function Returns Example result
date(value, ...) date as text 2025-03-15
time(value, ...) time as text 14:30:00
datetime(value, ...) date and time as text 2025-03-15 14:30:00
julianday(value, ...) days since 4714 BC, as a real number 2460749.5
unixepoch(value, ...) seconds since 1970-01-01, as an integer 1742049000
strftime(format, value, ...) text in any format you describe 15/03/2025

The value can be ISO text, the word 'now', or a number (a Julian day, or Unix seconds with the 'unixepoch' modifier). Let's see the current moment:

SQL The current date and time

Press Run (or Ctrl + Enter) to see the result here.

Your result depends on when you press Run. Note that 'now' is in UTC (Coordinated Universal Time), not your local time. Add the 'localtime' modifier to convert, though applications usually store UTC and convert only when displaying. The standard keywords CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP also work in SQLite and return the same UTC values.

Extracting parts of a date

date() and time() cut a datetime in two, and strftime() pulls out any part using format codes:

Code Meaning Example for 2025-02-14 20:57
%Y four-digit year 2025
%m month, 01โ€“12 02
%d day of month, 01โ€“31 14
%H, %M, %S hour (00โ€“23), minute, second 20, 57, 00
%w weekday, 0 = Sunday โ€ฆ 6 = Saturday 5
%u weekday, 1 = Monday โ€ฆ 7 = Sunday 5
%j day of year, 001โ€“366 045
%W week of year (weeks start on Monday) 06
%s Unix seconds 1739566620
%% a literal % %
SQL Taking dates apart ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

time() fills in the missing seconds (13:35:00), and order 12 was placed on a Friday (weekday 5).

strftime() returns text. strftime('%m', ordered_at) is the text '02', not the number 2, so strftime('%m', ordered_at) = 2 is false. Compare with '02', or convert first: CAST(strftime('%m', ordered_at) AS INTEGER) = 2.

SQLite has no format code for weekday or month names. A neat trick slices them out of a string with substr(): substr('SunMonTueWedThuFriSat', 1 + 3 * strftime('%w', ordered_at), 3) returns Fri for weekday 5. The CASE Expressions lesson shows a more readable way.

Formatting dates for people

The same codes rearrange a date into a local style. ISO text is the right format for storing and sorting, but people in different countries expect different layouts:

SQL Formatting dates ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

As with printf() in String Functions, format last: once a date becomes '22/10/2020', it no longer sorts or compares correctly.

Date arithmetic with modifiers

Instead of + and -, SQLite uses modifiers: extra text arguments applied from left to right.

Modifier Effect
'+7 days', '-3 hours', '+30 minutes', '+10 seconds' add or subtract an amount
'+1 month', '-2 years' add or subtract calendar months or years
'start of month', 'start of year', 'start of day' jump back to the beginning
'weekday N' move forward to the next weekday N (0 = Sunday), or stay if already there
'localtime', 'utc' convert between UTC and local time
'unixepoch' interpret a number as Unix seconds

Chaining modifiers answers questions that would otherwise need complicated code:

SQL Date arithmetic with modifiers

Press Run (or Ctrl + Enter) to see the result here.

The "last day of the month" recipe goes to the first of the month, forward one month, then back one day, and it handles leap years: February 2024 ends on the 29th. Adding two hours to 22:45 correctly rolls over to the next day.

Month arithmetic has one trap. Adding a month to January 31 can't land on February 31, so SQLite overflows into March: date('2025-01-31', '+1 month') is 2025-03-03. Since SQLite 3.46 you can add the 'floor' modifier to clamp to the end of the month instead: date('2025-01-31', '+1 month', 'floor') is 2025-02-28. Other databases clamp by default.

Modifiers work on columns too. Pending orders are due to ship within five days:

SQL Due dates for pending orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Differences between dates

To measure the gap between two dates, convert both to numbers and subtract. julianday() gives days (with fractions for times), and unixepoch() gives seconds:

SQL Employee tenure ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The query measures tenure on a fixed date (January 1, 2026) so the results stay the same every time; in a real report you'd write 'now'. Kofi Okafor has worked 3,593 days, about 9.8 years. Dividing by 365.25 is a good approximation that accounts for leap years.

timediff(a, b) (SQLite 3.43+) returns the exact calendar difference as text: +0009-10-00 00:00:00.000 means 9 years, 10 months and 0 days. It's handy for display; for math, stick to julianday() or unixepoch().

For durations in hours or minutes, subtract Unix seconds: (unixepoch('2025-03-15 22:45') - unixepoch('2025-03-15 20:00')) / 60 is 165 minutes.

Filtering date ranges correctly

Because ISO dates sort alphabetically in time order, plain comparisons work: hire_date < '2018-01-01' finds everyone hired before 2018. That only holds if every value uses the exact YYYY-MM-DD shape with leading zeros. '2025-3-15' < '2025-11-02' is false, because the text comparison sees '3' > '1', and date('2025-3-15') returns NULL because SQLite doesn't recognize the format.

The most common date bug involves BETWEEN and datetimes. Suppose you want all orders from February 14 through February 20:

SQL BETWEEN misses the last day ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns only 2 orders, while the second returns 4. The orders at '2025-02-20 15:16' and '2025-02-20 17:34' are greater than '2025-02-20' (a longer string that starts the same way sorts after it), so BETWEEN drops them. The fix is a half-open range: greater than or equal to the start, and strictly less than the day after the end. It works for dates, datetimes and timestamps with fractions of a second, in every database.

Keep the column bare in WHERE. WHERE strftime('%Y', ordered_at) = '2025' gives the right answer, but the database has to run the function on every row and can't use an index on ordered_at. WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01' returns the same rows and can use an index. The Writing Fast Queries lesson explains why.

Storing dates: three options

SQLite accepts three representations, and all the functions understand each of them:

Storage Example Good for
ISO 8601 text '2025-03-15 14:30:00' readability, the most common choice
Unix time integer 1742049000 compact, easy duration math
Julian day real 2460749.5 fractional-day calculations

Pick one per column and use it consistently. Text in ISO format is the safest default: it's readable in any tool and sorts correctly. Whatever you choose, store times in UTC. Also note that SQLite does little validation: date('2025-02-30') quietly returns 2025-03-02 instead of failing. A CHECK constraint such as CHECK (hire_date = date(hire_date)) rejects malformed values; see Constraints.

Dates in other databases

PostgreSQL, MySQL and SQL Server have real DATE, TIME and TIMESTAMP types, so you can add intervals with + and get errors for impossible dates. The concepts are the same, but every name differs:

Task SQLite PostgreSQL MySQL SQL Server
Today / now date('now'), datetime('now') CURRENT_DATE, now() CURDATE(), NOW() CAST(GETDATE() AS date), SYSDATETIME()
Add 7 days date(d, '+7 days') d + INTERVAL '7 days' DATE_ADD(d, INTERVAL 7 DAY) DATEADD(day, 7, d)
Year of a date strftime('%Y', d) EXTRACT(YEAR FROM d) YEAR(d) YEAR(d)
Days between julianday(b) - julianday(a) b - a (dates) DATEDIFF(b, a) DATEDIFF(day, a, b)
First of month date(d, 'start of month') date_trunc('month', d) DATE_FORMAT(d, '%Y-%m-01') DATETRUNC(month, d) (2022+)
Last of month date(d, 'start of month', '+1 month', '-1 day') date_trunc('month', d) + INTERVAL '1 month - 1 day' LAST_DAY(d) EOMONTH(d)
Format strftime('%d/%m/%Y', d) to_char(d, 'DD/MM/YYYY') DATE_FORMAT(d, '%d/%m/%Y') FORMAT(d, 'dd/MM/yyyy')
SQL
-- PostgreSQL
SELECT
  hire_date,
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  age(DATE '2026-01-01', hire_date) AS tenure,
  hire_date + INTERVAL '90 days' AS probation_ends
FROM employees;

-- MySQL
SELECT
  hire_date,
  YEAR(hire_date) AS hire_year,
  TIMESTAMPDIFF(YEAR, hire_date, '2026-01-01') AS full_years,
  DATE_ADD(hire_date, INTERVAL 90 DAY) AS probation_ends
FROM employees;

-- SQL Server
SELECT
  hire_date,
  YEAR(hire_date) AS hire_year,
  DATEDIFF(day, hire_date, '2026-01-01') AS days_employed,
  DATEADD(day, 90, hire_date) AS probation_ends
FROM employees;

MySQL has a few more helpers for date arithmetic. ADDDATE(d, 7) adds days and ADDDATE(d, INTERVAL 1 MONTH) is a synonym of DATE_ADD; SUBDATE subtracts. ADDTIME(t, '02:30:00') adds a time span to a time or datetime value, and SUBTIME subtracts one. Other databases use the + operator with an interval (PostgreSQL), DATEADD(hour, 2, t) (SQL Server) or the SQLite modifiers shown earlier:

SQL
-- MySQL, read-only
SELECT
  ADDDATE('2026-01-31', 7)                   AS plus_days,   -- 2026-02-07
  ADDDATE('2026-01-31', INTERVAL 1 MONTH)    AS plus_month,  -- 2026-02-28
  ADDTIME('2026-01-31 10:00:00', '02:30:00') AS plus_time;   -- 2026-01-31 12:30:00

The standard SQL forms CURRENT_DATE, CURRENT_TIMESTAMP and EXTRACT(YEAR FROM d) work in PostgreSQL and MySQL; EXTRACT isn't available in SQLite or SQL Server.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does date('2025-03-15', 'start of month', '+1 month', '-1 day') return?

    Modifiers apply left to right. Start of month gives 2025-03-01, plus one month gives 2025-04-01, minus one day gives 2025-03-31.
  2. 2. Why does WHERE ordered_at BETWEEN '2025-02-14' AND '2025-02-20' miss an order placed at '2025-02-20 15:16'?

    BETWEEN includes its ends, but '2025-02-20 15:16' is greater than '2025-02-20'. Use >= '2025-02-14' AND < '2025-02-21' instead.
  3. 3. What does strftime('%m', '2025-02-14') = 2 evaluate to?

    strftime() returns the text '02', which isn't equal to the number 2. Compare with '02' or cast the result to INTEGER.
  4. 4. In SQLite, what does date('2025-01-31', '+1 month') return?

    February 31 doesn't exist, so SQLite overflows three days into March. Add the 'floor' modifier (SQLite 3.46+) to get 2025-02-28.
  5. 5. Which expression gives the number of days between two dates in SQLite?

    julianday() turns a date into a day number, so subtracting gives the gap in days (285 here). Subtracting text values just converts each to its leading number, 2025, giving 0.

Summary

  • SQLite stores dates as ISO 8601 text (YYYY-MM-DD HH:MM:SS), Unix seconds or Julian days; ISO text is the usual choice.
  • date(), time(), datetime(), julianday(), unixepoch() and strftime() read and convert those values; 'now' is UTC.
  • strftime() format codes (%Y, %m, %d, %H, %w, โ€ฆ) extract or reformat parts and always return text.
  • Modifiers such as '+7 days', '-1 month', 'start of month' and 'weekday 1' do date arithmetic, applied left to right.
  • Subtract julianday() values for days and unixepoch() values for seconds; timediff() gives a readable difference.
  • Filter ranges with >= start AND < next_day, not BETWEEN, and keep the column bare so indexes can help.
  • Other databases have real date types with INTERVAL, DATEADD, DATEDIFF, EXTRACT and to_char/FORMAT.

Next: CASE Expressions, where you'll add if-then-else logic to your queries.