SQL Date Functions: Current Date, Extract Year and Month, Add Days, Date Differences

Dates are everywhere in real data: when an order was placed, when someone joined, when a subscription ends. SQL can extract parts of dates, do date arithmetic, and group by periods. Date functions are also the least standard part of SQL, so this lesson shows each idea in the major databases.

The example tables#

employees

orders

Today's date and time#

CURRENT_TIMESTAMP is standard SQL and works almost everywhere.

Filtering by date#

Dates in ISO format ('YYYY-MM-DD') can be compared directly:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28';

order_id | order_date
---------+-----------
102      | 2026-02-11
103      | 2026-02-20

Extracting the year, month or day#

In SQLite:

SELECT name,
       hire_date,
       strftime('%Y', hire_date) AS hire_year,
       strftime('%m', hire_date) AS hire_month
FROM employees
ORDER BY hire_date;

name  | hire_date  | hire_year | hire_month
------+------------+-----------+-----------
Meera | 2018-01-10 | 2018      | 01
Ravi  | 2019-07-01 | 2019      | 07
Priya | 2020-05-04 | 2020      | 05
Asha  | 2021-03-15 | 2021      | 03
John  | 2022-11-20 | 2022      | 11

The same in PostgreSQL: EXTRACT(YEAR FROM hire_date) AS hire_year.

Grouping by month#

A monthly revenue report is one of the most common queries in business:

-- SQLite
SELECT strftime('%Y-%m', order_date) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

month   | revenue
--------+--------
2026-01 | 250
2026-02 | 680
2026-03 | 90

Equivalent "month" expressions:

Adding and subtracting time#

A due date 30 days after each order:

-- SQLite
SELECT order_id,
       order_date,
       date(order_date, '+30 days') AS due_date
FROM orders;

order_id | order_date | due_date
---------+------------+-----------
101      | 2026-01-05 | 2026-02-04
102      | 2026-02-11 | 2026-03-13
103      | 2026-02-20 | 2026-03-22
104      | 2026-03-02 | 2026-04-01

Use negative values (or - / DATE_SUB) to go back in time.

Difference between two dates#

How many days each employee has worked, measured from 23 October 2026:

-- SQLite
SELECT name,
       hire_date,
       CAST(julianday('2026-10-23') - julianday(hire_date) AS INTEGER) AS days_employed
FROM employees
ORDER BY days_employed DESC;

name  | hire_date  | days_employed
------+------------+--------------
Meera | 2018-01-10 | 3208
Ravi  | 2019-07-01 | 2671
Priya | 2020-05-04 | 2363
Asha  | 2021-03-15 | 2048
John  | 2022-11-20 | 1433

In practice you would use the current date instead of a fixed one:

Time zones#

Store timestamps in UTC (or in a TIMESTAMP WITH TIME ZONE column) and convert to the user's local time only when displaying. Mixing local times from different places in one column makes comparisons and durations wrong.

Common mistakes#

Practice#

SELECT strftime('%Y', hire_date) AS year,
       COUNT(*) AS hires
FROM employees
WHERE hire_date >= '2019-01-01'
GROUP BY year
ORDER BY year;

year | hires
-----+------
2019 | 1
2020 | 1
2021 | 1
2022 | 1

Key takeaway#