Strings and dates Updated November 2026

Date functions

Q: How do I get the current date in SQL?

CURRENT_DATE works in SQLite and PostgreSQL. SQLite also has date('now'); PostgreSQL also has NOW() for a timestamp. In SQLite 'now' is UTC; add 'localtime' for local time.

Q: How do I group by month in SQL?

Convert each date to its month and group on that: strftime('%Y-%m', d) in SQLite, date_trunc('month', d) or to_char(d, 'YYYY-MM') in PostgreSQL.

Q: How do I calculate the number of days between two dates?

PostgreSQL: later_date - earlier_date for DATE columns. SQLite: julianday(later) - julianday(earlier). MySQL: DATEDIFF(later, earlier).

Date handling differs by dialect. SQLite stores dates as ISO text and uses date('now'), strftime('%Y-%m', d) and julianday(a) - julianday(b); PostgreSQL has real DATE types with CURRENT_DATE, EXTRACT(YEAR FROM d), date_trunc('month', d), d + INTERVAL '7 days' and a - b for days between. Keep dates as YYYY-MM-DD so comparisons and sorting work.

Syntax

SQLite: date('now'), strftime(format, d), date(d, '+7 days'), julianday(d)   PostgreSQL: CURRENT_DATE, EXTRACT(part FROM d), date_trunc('month', d), d + INTERVAL '7 days'

Examples

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

Result: month | revenue 2025-01 | 12500 2025-02 | 9800

SQLite. PostgreSQL: to_char(order_date, 'YYYY-MM') or date_trunc('month', order_date).

SELECT * FROM orders WHERE order_date >= date('now', '-30 days');

Result: Orders from the last 30 days (SQLite)

PostgreSQL: WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'.

SELECT id, julianday(shipped_date) - julianday(order_date) AS days_to_ship FROM orders;

Result: id | days_to_ship 1 | 3.0

PostgreSQL: shipped_date - order_date gives an integer number of days for DATE columns.

Common mistakes

Storing dates as DD-MM-YYYY text in SQLite breaks sorting and comparisons; use YYYY-MM-DD.
Using NOW(), GETDATE() or DATEADD in SQLite raises no such function; use date('now') and date(d, '+1 month').
Comparing a timestamp column with a date string (WHERE ts = '2025-01-31') matches only midnight; use a range >= day AND < next day.

Practice Date functions

These drills check your answer on live data, free, no account needed.

Revenue by monthReturn each month as text like 2025-01 together with the total of its paid orders. Hint for SQLite: strftime('%Y-%m', order_date) gives the month.
Hires per yearReturn each hire year (like 2021) and how many employees were hired that year. strftime('%Y', hire_date) gives the year.
Active in several monthsFor customers who ordered in at least two different months, return the customer_id and the number of different months they ordered in. Count every order, whatever the status.

Questions

How do I get the current date in SQL?