SQL Date Functions: Syntax, Examples & Practice

A date function takes a date and returns something useful about it: its year, the date 30 days later, the number of days since it happened. Here are two of them on real data — the year of each order, and the date 30 days after it:

SELECT id, order_date,
       strftime('%Y', order_date)   AS order_year,
       date(order_date, '+30 days') AS plus_30_days
FROM orders
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

For every order, strftime('%Y', …) extracts the year and date(…, '+30 days') returns the date 30 days later. The second function does real calendar arithmetic: 30 days after 15 March is 14 April, and 30 days after 15 February 2023 is 17 March, because February has 28 days. That query is written for SQLite. In SQL Server the same two columns are YEAR(order_date) and DATEADD(day, 30, order_date); the comparison table below gives the MySQL and PostgreSQL versions too.

Every operation this lesson covers, with the function each database uses for it. d is a date column; a is the earlier date and b the later one.

What Date Functions Do

Date functions are the part of SQL that understands calendars. A column such as order_date is only a value until you ask a question about it — which year? how long ago? what is the date 30 days later? — and date functions answer those questions inside the query. They cover five everyday operations:

• Get the current date or timestamp — today's date, or the date and time right now.
• Extract date parts — pull the year, month, day or weekday out of a date.
• Date arithmetic — add or subtract days, months or years.
• Date difference — count the days, months or years between two dates.
• Date filtering, grouping and formatting — select a date range, build monthly reports, change how a date is displayed.

Basic Date Function Syntax

A date function takes a date (and usually an instruction) and returns a new value:

function_name(date_value, instruction)

strftime('%Y', order_date)       -- extract: the year of order_date
date(order_date, '+30 days')     -- arithmetic: 30 days after order_date

The Four SQLite Date Functions

The SQLabHub Playground runs SQLite, which does everything with four functions and a list of modifiers:

Modifiers are short strings such as '+30 days', '-1 year', 'start of month' and 'localtime'. Several can be chained and they are applied left to right.

Before using date functions, know what kind of value you are working with.

CREATE TABLE deliveries (
  order_id      INT PRIMARY KEY,
  order_date    DATE,
  placed_at     TIMESTAMP,
  delivery_date DATE
);

The anchor for every "today", "this month" and "last 30 days" query.

SQL CURRENT_DATE

SELECT CURRENT_DATE;

SQL NOW() and CURRENT_TIMESTAMP

SELECT NOW();

Current Date in the Playground (SQLite)

SELECT date('now')                  AS today_utc,
       datetime('now')              AS right_now_utc,
       datetime('now', 'localtime') AS right_now_local;

▶ Run this query in the SQL Playground

The result changes every time you run it, so no fixed output is shown here. Note the column names: SQLite's 'now' — and its CURRENT_DATE — are in UTC. Add the 'localtime' modifier to convert to the machine's local time zone.

Pulling the year, month or day out of a date — the first step of almost every report.

SQL YEAR(), MONTH() and DAY()

SELECT order_date,
       YEAR(order_date)  AS yr,
       MONTH(order_date) AS mo,
       DAY(order_date)   AS dy
FROM orders
ORDER BY id;

SQL EXTRACT()

SELECT id, order_date,
       EXTRACT(YEAR FROM order_date)  AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

strftime() in SQLite

SQLite has neither YEAR() nor EXTRACT(). It uses strftime(format, date), where the format string names the part you want:

SELECT id, order_date,
       strftime('%Y', order_date) AS order_year,
       strftime('%m', order_date) AS order_month,
       strftime('%d', order_date) AS order_day
FROM orders
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

EXTRACT vs YEAR / MONTH / DAY: Which Database Supports What

Shifting a date forward or backward — expiry dates, deadlines, "30 days ago".

SQL DATEADD() — SQL Server

SELECT id, order_date,
       DATEADD(day, 30, order_date) AS thirty_days_later
FROM orders
ORDER BY id;

SELECT DATEADD(month, 6, start_date) AS six_months_later,
       DATEADD(year, 1, start_date)  AS one_year_later
FROM subscriptions;

DATE_ADD() in MySQL and INTERVAL in PostgreSQL

SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS renewal
FROM orders;

SELECT order_date + INTERVAL '30 days' AS renewal
FROM orders;

MySQL also has DATE_SUB() for going backward. In PostgreSQL, adding a whole number to a date (order_date + 30) adds that many days and returns a date; adding an INTERVAL returns a timestamp.

Date Modifiers in SQLite

SELECT id, order_date,
       date(order_date, '+30 days') AS plus_30_days,
       date(order_date, '+1 month') AS plus_1_month,
       date(order_date, '-1 year')  AS minus_1_year
FROM orders
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

"30 days" and "1 month" are not the same thing: for the order on 15 March, 30 days later is 14 April but one month later is 15 April.

DATEADD Business Examples

Five real scenarios that are all the same pattern underneath. These use SQL Server syntax on illustrative tables.

SELECT customer, plan, start_date,
       DATEADD(month, 12, start_date) AS expiry_date
FROM subscriptions;

SELECT id, order_date,
       DATEADD(day, 6, order_date) AS estimated_delivery
FROM orders;

SELECT customer, renewal_date
FROM subscriptions
WHERE renewal_date <= DATEADD(day, 7, CAST(GETDATE() AS date));

SELECT id, order_date,
       DATEADD(year, 1, order_date) AS warranty_expiry
FROM orders;

SELECT first_name, last_name, hire_date,
       DATEADD(day, 90, hire_date) AS probation_end
FROM employees;

Measuring the gap between two dates — one of the most common SQL interview topics.

SQL DATEDIFF() — SQL Server

SELECT order_id, order_date, delivery_date,
       DATEDIFF(day, order_date, delivery_date) AS delivery_days
FROM deliveries;

DATEDIFF in MySQL and Date Subtraction in PostgreSQL

-- end date first, result always in days
SELECT DATEDIFF(delivery_date, order_date) AS delivery_days
FROM deliveries;

-- subtracting two dates gives days
SELECT delivery_date - order_date AS delivery_days
FROM deliveries;

MySQL's DATEDIFF has the same name as SQL Server's but a different signature: two arguments, end date first, days only. For months or years in MySQL use TIMESTAMPDIFF(MONTH, start_date, end_date).

Date Differences in SQLite: julianday()

julianday() converts a date into a continuous day number, so subtracting two of them gives the days between the dates. This example measures each employee's tenure as of 1 January 2025:

SELECT id, first_name, hire_date,
       CAST(julianday('2025-01-01') - julianday(hire_date) AS INTEGER) AS tenure_days
FROM employees
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

William was hired on 24 July 2022, which is 892 days before 1 January 2025. Replace '2025-01-01' with 'now' to measure against today instead.

DATEDIFF Practical Examples

Five business questions built on a date difference. SQL Server syntax on illustrative tables; GETDATE() is the current date and time.

SELECT user_id, MAX(order_date) AS last_order,
       DATEDIFF(day, MAX(order_date), GETDATE()) AS days_since_last_order
FROM orders
GROUP BY user_id;

SELECT first_name, last_name, hire_date,
       DATEDIFF(day, hire_date, GETDATE()) AS tenure_days
FROM employees;

SELECT user_id,
       DATEDIFF(day, MIN(order_date), MAX(order_date)) AS retention_days
FROM orders
GROUP BY user_id;

SELECT project_id,
       DATEDIFF(day, start_date, end_date) AS project_duration_days
FROM projects;

SELECT order_id,
       DATEDIFF(day, order_date, delivery_date) AS delivery_days
FROM deliveries
WHERE DATEDIFF(day, order_date, delivery_date) > 7;

Calculating Age in SQL

Customer age, employee age, years of service — all the same calculation: count the full years between a start date and today. The first two queries assume a members table with a dob (date of birth) column.

SELECT first_name, last_name, dob,
       DATEDIFF(day, dob, GETDATE()) / 365.25 AS approx_age
FROM members;

SELECT first_name, last_name, dob,
       DATEDIFF(year, dob, GETDATE())
       - CASE WHEN DATEADD(year, DATEDIFF(year, dob, GETDATE()), dob) > GETDATE()
              THEN 1 ELSE 0 END AS exact_age
FROM members;

The Playground's employees table has no date of birth, but the same "full years" logic applies to hire_date — completed years of service as of 1 January 2025:

SELECT first_name, hire_date,
       CAST(strftime('%Y', '2025-01-01') AS INTEGER)
       - CAST(strftime('%Y', hire_date) AS INTEGER)
       - (strftime('%m-%d', '2025-01-01') < strftime('%m-%d', hire_date)) AS full_years
FROM employees
ORDER BY id
LIMIT 4;

▶ Run this query in the SQL Playground

The comparison in the last line is 1 when the anniversary (month and day) has not been reached yet, which subtracts the incomplete year. William's 2025 − 2022 = 3 becomes 2 because 24 July comes after 1 January.

Selecting a day, a month, a year or a moving window.

To filter records by date, compare the date column in a WHERE clause. The most reliable pattern is a half-open range: from the first day you want, up to but not including the first day you don't.

SELECT COUNT(*) AS orders_in_2024
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

▶ Run this query in the SQL Playground

This works unchanged in MySQL, PostgreSQL and SQL Server. It also stays correct when the column holds a time of day: BETWEEN '2024-01-01' AND '2024-12-31' would miss anything timestamped after midnight on 31 December. The SQL BETWEEN lesson explains why its upper bound is inclusive.

Filtering for One Exact Date

For a single day, compare the column with a date written as 'YYYY-MM-DD':

SELECT id, user_id, order_date, total
FROM orders
WHERE order_date = '2024-04-24'
ORDER BY id;

▶ Run this query in the SQL Playground

Filtering With a Function on the Column

SELECT *
FROM orders
WHERE YEAR(order_date) = 2026;

Rolling 30-Day Window

A rolling window moves with the calendar instead of using fixed months. In production the anchor is today; in the Playground we anchor on the newest order date, 2024-12-24:

SELECT COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date >  date('2024-12-24', '-30 days')
  AND order_date <= '2024-12-24';

▶ Run this query in the SQL Playground

SELECT SUM(amount) AS rolling_30_day_sales
FROM sales
WHERE sale_date >  DATEADD(day, -30, CAST(GETDATE() AS date))
  AND sale_date <= CAST(GETDATE() AS date);

Current Month Sales (Month to Date)

Month-to-date revenue is "everything since the first day of the current month". Here the anchor is the newest order in the table, found with a subquery:

SELECT COUNT(*) AS orders, ROUND(SUM(total), 2) AS mtd_revenue
FROM orders
WHERE order_date >= date((SELECT MAX(order_date) FROM orders), 'start of month');

▶ Run this query in the SQL Playground

SELECT SUM(amount) AS mtd_revenue
FROM sales
WHERE sale_date >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

The monthly report — the single most common date query in business SQL.

Extract the part of the date you want to report on, then GROUP BY it and summarise each group with an aggregate function.

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)             AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE order_date >= '2024-07-01' AND order_date < '2025-01-01'
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

▶ Run this query in the SQL Playground

Each order is labelled with its year and month ('2024-07'), rows with the same label are grouped, and SUM and COUNT run once per group. Sorting with ORDER BY works because YYYY-MM text sorts chronologically.

SELECT YEAR(sale_date) AS yr, MONTH(sale_date) AS mo, SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY yr, mo;

Date Functions with Aggregate Functions

SELECT strftime('%Y', order_date) AS year,
       COUNT(*)             AS orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
GROUP BY strftime('%Y', order_date)
ORDER BY year;

▶ Run this query in the SQL Playground

Month-over-Month (MoM) Analysis

MoM compares each month with the one before it. Build the monthly totals first, then read the previous row with the window function LAG(), covered step by step in SQL LAG and LEAD:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month
FROM (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2024-09-01'
  GROUP BY strftime('%Y-%m', order_date)
) AS monthly;

▶ Run this query in the SQL Playground

Year-over-Year (YoY) Analysis

YoY compares a period with the same period one year earlier, which removes seasonal effects. Conditional aggregation puts both numbers in one row:

SELECT ROUND(SUM(CASE WHEN strftime('%Y-%m', order_date) = '2024-12' THEN total ELSE 0 END), 2) AS dec_2024,
       ROUND(SUM(CASE WHEN strftime('%Y-%m', order_date) = '2023-12' THEN total ELSE 0 END), 2) AS dec_2023
FROM orders;

▶ Run this query in the SQL Playground

SELECT
  SUM(CASE WHEN YEAR(sale_date) = 2026 AND MONTH(sale_date) = 7 THEN amount ELSE 0 END) AS july_2026,
  SUM(CASE WHEN YEAR(sale_date) = 2025 AND MONTH(sale_date) = 7 THEN amount ELSE 0 END) AS july_2025
FROM sales;

Presenting a date the way a report or region expects to see it.

SELECT id, order_date,
       strftime('%d-%m-%Y', order_date) AS dd_mm_yyyy,
       strftime('%m/%d/%Y', order_date) AS us_style
FROM orders
ORDER BY id
LIMIT 3;

▶ Run this query in the SQL Playground

SELECT DATE_FORMAT(order_date, '%d-%m-%Y') AS formatted_date
FROM orders;

SELECT TO_CHAR(order_date, 'DD-MM-YYYY') AS formatted_date
FROM orders;

SELECT FORMAT(order_date, 'dd-MM-yyyy') AS formatted_date
FROM orders;

First Day and Last Day of the Month

Clean month boundaries are essential for billing cycles and month-end reports.

SELECT date('2024-12-24', 'start of month')                        AS month_start,
       date('2024-12-24', 'start of month', '+1 month', '-1 day') AS month_end;

▶ Run this query in the SQL Playground

The modifiers run left to right: jump to the 1st, add a month, step back one day. The same chain gives 2024-02-29 for a February 2024 date, so leap years are handled for you.

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS month_start,
       EOMONTH(GETDATE())                                 AS month_end;

Real-world dates are messy — the five problems to check for first.

Detecting Values That Are Not Real Dates

-- rows whose text does not parse as a date
SELECT raw_date_text
FROM legacy_orders
WHERE TRY_CAST(raw_date_text AS date) IS NULL;

In SQLite, date(value) returns NULL for text it cannot read (date('12-07-2026') and date('2026-13-01') are both NULL), so WHERE date(col) IS NULL does the same job. Be careful with impossible days: date('2026-02-30') is not rejected — it rolls forward to 2026-03-02.

A Real Data-Quality Check in the Playground

Dates can be perfectly formatted and still be wrong. An order cannot ship before it was placed, so this query looks for exactly that:

SELECT id, order_date, shipped_date
FROM orders
WHERE shipped_date < order_date
ORDER BY id;

▶ Run this query in the SQL Playground

The Playground's practice data has 38 such orders — its shipped_date values are randomly generated, so do not read shipping times from them. Finding rows like these before building a delivery-time report is precisely what date cleaning is for.

35 real business scenarios across sales, retention, subscriptions, banking, insurance, healthcare, SaaS and more.

SELECT sale_date, SUM(amount) AS daily_total
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

SELECT DATEADD(day, 1 - DATEPART(weekday, sale_date), sale_date) AS week_start,
       SUM(amount) AS weekly_total
FROM sales
GROUP BY DATEADD(day, 1 - DATEPART(weekday, sale_date), sale_date)
ORDER BY week_start;
-- the first day of the week follows the server's DATEFIRST setting

SELECT YEAR(sale_date) AS yr, MONTH(sale_date) AS mo, SUM(amount) AS monthly_revenue
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY yr, mo;

SELECT YEAR(sale_date) AS yr,
       DATEPART(quarter, sale_date) AS quarter,
       SUM(amount) AS quarterly_revenue
FROM sales
GROUP BY YEAR(sale_date), DATEPART(quarter, sale_date)
ORDER BY yr, quarter;

SELECT YEAR(sale_date) AS yr, SUM(amount) AS annual_revenue
FROM sales
GROUP BY YEAR(sale_date)
ORDER BY yr;

SELECT YEAR(signup_date) AS yr, MONTH(signup_date) AS mo,
       COUNT(*) AS signups,
       SUM(CASE WHEN last_active_date >= DATEADD(day, -30, @today) THEN 1 ELSE 0 END) AS still_active
FROM customers
GROUP BY YEAR(signup_date), MONTH(signup_date);

SELECT customer, plan, renewal_date
FROM subscriptions
WHERE renewal_date < @today;

SELECT customer, plan, renewal_date
FROM subscriptions
WHERE renewal_date BETWEEN @today AND DATEADD(day, 7, @today);

SELECT oi.order_id, p.name AS product_name, o.order_date,
       DATEADD(year, 1, o.order_date) AS warranty_expiry,
       CASE WHEN @today <= DATEADD(year, 1, o.order_date) THEN 'Active' ELSE 'Expired' END AS warranty_status
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id;

SELECT batch_id, received_date,
       DATEDIFF(day, received_date, @today) AS days_in_stock
FROM inventory_batches
ORDER BY days_in_stock DESC;

SELECT first_name, last_name, hire_date,
       DATEDIFF(year, hire_date, @today)
       - CASE WHEN DATEADD(year, DATEDIFF(year, hire_date, @today), hire_date) > @today
              THEN 1 ELSE 0 END AS tenure_years
FROM employees;
-- DATEDIFF(year, …) counts year boundaries, so subtract 1 until the anniversary has passed

SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR(DATEADD(year, 5, hire_date)) = YEAR(@today)
  AND MONTH(DATEADD(year, 5, hire_date)) = MONTH(@today);

SELECT user_id, MAX(order_date) AS last_order_date,
       DATEDIFF(day, MAX(order_date), @today) AS days_since_last_order
FROM orders
GROUP BY user_id
HAVING DATEDIFF(day, MAX(order_date), @today) > 90;

SELECT id, user_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-10-15' AND '2024-11-05';

SELECT transaction_id, transaction_date, amount
FROM transactions
WHERE transaction_date >= '2026-04-01' AND transaction_date < '2027-04-01';

SELECT claim_id, policy_start_date, claim_date,
       DATEDIFF(day, policy_start_date, claim_date) AS days_since_policy_start
FROM claims
WHERE DATEDIFF(day, policy_start_date, claim_date) <= 30;

SELECT patient_name, doctor_name, appointment_date
FROM appointments
WHERE appointment_date BETWEEN @today AND DATEADD(day, 7, @today);

SELECT order_id, order_date, delivery_date,
       DATEDIFF(day, order_date, delivery_date) AS delivery_days
FROM deliveries
WHERE DATEDIFF(day, order_date, delivery_date) > 7;

SELECT SUM(amount) AS trailing_12mo_revenue
FROM sales
WHERE sale_date >= DATEADD(month, -12, @today);

SELECT SUM(amount) AS mtd_revenue
FROM sales
WHERE YEAR(sale_date) = YEAR(@today)
  AND MONTH(sale_date) = MONTH(@today);

SELECT DATEDIFF(day, @today, EOMONTH(@today)) AS days_remaining_this_month;

SELECT product_name, launch_date
FROM product_launches
WHERE YEAR(launch_date) = YEAR(@today)
  AND DATEPART(quarter, launch_date) = DATEPART(quarter, @today);

SELECT store_id, SUM(amount) AS last_month_sales
FROM sales
WHERE sale_date >= DATEADD(month, -1, DATEFROMPARTS(YEAR(@today), MONTH(@today), 1))
  AND sale_date <  DATEFROMPARTS(YEAR(@today), MONTH(@today), 1)
GROUP BY store_id;

SELECT branch_id,
  SUM(CASE WHEN YEAR(sale_date) = YEAR(@today)
            AND sale_date <= @today THEN amount ELSE 0 END) AS this_year,
  SUM(CASE WHEN YEAR(sale_date) = YEAR(@today) - 1
            AND sale_date <= DATEADD(year, -1, @today) THEN amount ELSE 0 END) AS last_year
FROM sales
GROUP BY branch_id;

SELECT location_name, opened_date
FROM franchise_locations
WHERE opened_date >= DATEADD(month, -24, @today);

SELECT supplier_id, AVG(1.0 * DATEDIFF(day, po_date, received_date)) AS avg_lead_time_days
FROM purchase_orders
GROUP BY supplier_id;

SELECT id, user_id, status, order_date
FROM orders
WHERE shipped_date IS NULL
  AND status IN ('pending', 'processing')
  AND order_date < DATEADD(day, -7, @today);

SELECT user_id,
       DATEDIFF(day, MIN(order_date), MAX(order_date)) AS lifespan_days,
       SUM(total) AS lifetime_value
FROM orders
GROUP BY user_id;

SELECT YEAR(first_order_date) AS cohort_year, MONTH(first_order_date) AS cohort_month,
       COUNT(DISTINCT user_id) AS cohort_size
FROM (SELECT user_id, MIN(order_date) AS first_order_date FROM orders GROUP BY user_id) t
GROUP BY YEAR(first_order_date), MONTH(first_order_date);

SELECT activity_date, COUNT(DISTINCT user_id) AS daily_active_users
FROM user_activity_log
GROUP BY activity_date
ORDER BY activity_date;

SELECT account_id, balance
FROM account_snapshots
WHERE snapshot_date = EOMONTH(@today, -1);

SELECT SUM(amount) AS qtd_spend
FROM expenses
WHERE YEAR(expense_date) = YEAR(@today)
  AND DATEPART(quarter, expense_date) = DATEPART(quarter, @today);

SELECT vendor_name, invoice_id, due_date, amount
FROM payables
WHERE due_date BETWEEN @today AND DATEADD(day, 7, @today);

SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mo,
       AVG(1.0 * DATEDIFF(day, order_date, delivery_date)) AS avg_fulfillment_days
FROM deliveries
GROUP BY YEAR(order_date), MONTH(order_date);

SELECT YEAR(renewal_date) AS yr, MONTH(renewal_date) AS mo, COUNT(*) AS renewals_due,
       SUM(monthly_price) AS mrr_at_risk
FROM subscriptions
GROUP BY YEAR(renewal_date), MONTH(renewal_date);

Why Do We Need Date Functions?

Date Functions for Data Analysts

Where date arithmetic and date differences show up in day-to-day BI work.

7 mistakes that trip up almost every SQL learner working with dates — the wrong version, the fix, and why it matters.

SELECT * FROM orders
WHERE order_date = '03-04-2026';
-- is this March 4th or April 3rd? Ambiguous without knowing the format

SELECT * FROM orders
WHERE order_date = '2026-04-03'; -- YYYY-MM-DD is unambiguous

SELECT COUNT(*) FROM orders
WHERE order_date > '2024-3-15';
-- returns 0 rows in the Playground: '2024-3-15' is not zero-padded,
-- so the text comparison sorts it after every real date in 2024

SELECT COUNT(*) FROM orders
WHERE order_date > '2024-03-15';
-- returns 47: zero-padded YYYY-MM-DD text sorts chronologically

SELECT * FROM orders
WHERE order_date = date('now');
-- date('now') is the current date in UTC, which can be
-- yesterday or tomorrow in the business's local time zone

SELECT * FROM orders
WHERE order_date = date('now', 'localtime');
-- convert to local time before taking the date

SELECT DATEDIFF(day, delivery_date, order_date) AS delivery_days
FROM deliveries;
-- arguments reversed — produces a negative delivery time

SELECT DATEDIFF(day, order_date, delivery_date) AS delivery_days
FROM deliveries;

SELECT DATEADD(day, 30, GETDATE()) AS orders_last_30_days;
-- meant to look BACK 30 days, but added 30 days INTO THE FUTURE instead

SELECT DATEADD(day, -30, GETDATE()) AS thirty_days_ago;

SELECT MONTH(order_date) AS mo, SUM(total) AS revenue
FROM orders
GROUP BY MONTH(order_date);
-- merges January 2022, 2023 and 2024 into a single "month 1" row

SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mo, SUM(total) AS revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

SELECT * FROM sales
WHERE YEAR(sale_date) = 2026;
-- correct result, but cannot use a standard index on sale_date

SELECT * FROM sales
WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01';

SQLite vs MySQL vs PostgreSQL vs SQL Server — exactly where the syntax diverges.

The same task — a renewal date 30 days after an order — in each database:

SELECT date(order_date, '+30 days') AS renewal
FROM orders;

SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS renewal
FROM orders;

SELECT DATEADD(day, 30, order_date) AS renewal
FROM orders;

SELECT order_date + INTERVAL '30 days' AS renewal
FROM orders;

Keeping date-heavy queries fast, even at millions of rows.

Checking the Query Plan

EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE order_date >= '2024-01-01';

A query plan shows whether the database can jump straight to matching rows through an index (a search or index range scan) or must read every row (a scan). MySQL and PostgreSQL use EXPLAIN; SQL Server shows an execution plan. The Playground's small tables have no index on order_date, so both forms of the filter scan there — the difference appears on large, indexed tables, and it grows with table size.

Quick revision before an interview — and the format codes you will reuse constantly.

SQLite strftime() Format Codes

MySQL's DATE_FORMAT() uses similar percent codes; PostgreSQL's TO_CHAR() and SQL Server's FORMAT() use letter patterns such as YYYY-MM-DD and yyyy-MM-dd.

Learn → Practice — 8 exercises, easy to challenging, on the real Playground tables.

Every exercise uses a table and columns that exist in the SQLabHub SQL Playground, and every solution has been run against that database. Write your own query first, run it, then compare.

1. 1Easy For every employee, show the name, the hire date, and the hire year and hire month as separate columns. Sort by hire date. Table: employees

   Show solution

   SQLite · runs in the Playground

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

   ▶ Run this query in the SQL Playground

   strftime() formats a date using a pattern: %Y is the four-digit year and %m the two-digit month. The first row is Steven Nguyen, hired 2015-01-14 → 2015, 01.

2. 2Easy List every order placed in March 2024, oldest first. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, order_date, total
   FROM orders
   WHERE order_date >= '2024-03-01'
     AND order_date <  '2024-04-01'
   ORDER BY order_date;

   ▶ Run this query in the SQL Playground

   A half-open range (from the 1st, up to but not including the 1st of the next month) is the safest way to select a month. It returns 6 orders.

3. 3Medium Each order can be returned within 30 days. Show each order with its return deadline, newest orders first. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, order_date,
          date(order_date, '+30 days') AS return_deadline
   FROM orders
   ORDER BY order_date DESC;

   ▶ Run this query in the SQL Playground

   date() accepts modifiers that shift the value. The newest order (2024-12-24) gets a deadline of 2025-01-23 — the function rolls over the month and year for you.

4. 4Medium Build a monthly revenue report for 2023: one row per month with the number of orders and total revenue. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT strftime('%Y-%m', order_date) AS month,
          COUNT(*)              AS orders,
          ROUND(SUM(total), 2)  AS revenue
   FROM orders
   WHERE order_date >= '2023-01-01'
     AND order_date <  '2024-01-01'
   GROUP BY strftime('%Y-%m', order_date)
   ORDER BY month;

   ▶ Run this query in the SQL Playground

   Grouping by '%Y-%m' keeps the year and month together, so months from different years can never merge. You should get 12 rows, starting with 2023-01 (3 orders, 10518.82).

5. 5Medium Find the five longest-serving employees and their tenure in years (one decimal) as of 1 January 2025. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, hire_date,
          ROUND((julianday('2025-01-01') - julianday(hire_date)) / 365.25, 1) AS tenure_years
   FROM employees
   ORDER BY hire_date
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   julianday() turns a date into a day number, so subtracting two of them gives the days between the dates. Dividing by 365.25 converts days to years. Steven Nguyen comes first with 10 years.

6. 6Medium Which day of the week gets the most orders? Show each weekday name with its order count, busiest first. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT CASE strftime('%w', order_date)
            WHEN '0' THEN 'Sunday'    WHEN '1' THEN 'Monday'
            WHEN '2' THEN 'Tuesday'   WHEN '3' THEN 'Wednesday'
            WHEN '4' THEN 'Thursday'  WHEN '5' THEN 'Friday'
            ELSE 'Saturday' END AS weekday,
          COUNT(*) AS orders
   FROM orders
   GROUP BY strftime('%w', order_date)
   ORDER BY orders DESC;

   ▶ Run this query in the SQL Playground

   %w returns the day of the week as text from '0' (Sunday) to '6' (Saturday), and CASE WHEN turns it into a name. Friday is the busiest day with 37 orders.

7. 7Challenging The newest order in the table is dated 2024-12-24. Treating that as “today”, find the customers (user_id) whose last order was more than 365 days earlier, most inactive first. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT user_id,
          MAX(order_date) AS last_order,
          CAST(julianday('2024-12-24') - julianday(MAX(order_date)) AS INTEGER) AS days_inactive
   FROM orders
   GROUP BY user_id
   HAVING days_inactive > 365
   ORDER BY days_inactive DESC;

   ▶ Run this query in the SQL Playground

   MAX(order_date) finds each customer’s latest order, julianday() measures the gap, and HAVING filters on the aggregated result. 57 customers qualify; the most inactive is user 15 (last order 2022-01-15, 1074 days).

8. 8Challenging For each month of 2024, show revenue, the previous month’s revenue, and the month-over-month change as a percentage. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT month, revenue,
          LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
          ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
                / LAG(revenue) OVER (ORDER BY month), 1) AS mom_change_pct
   FROM (
     SELECT strftime('%Y-%m', order_date) AS month,
            ROUND(SUM(total), 2) AS revenue
     FROM orders
     WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
     GROUP BY strftime('%Y-%m', order_date)
   ) AS monthly
   ORDER BY month;

   ▶ Run this query in the SQL Playground

   The inner query builds monthly totals; LAG() then reads the previous row’s revenue. January has no previous month, so its change is NULL; February is +4.2% (16998.61 vs 16318.57).

50 questions grouped by level — select any question to reveal the answer.

Beginner

Intermediate

Advanced

Key takeaways from this lesson.