SQL Date Functions

Date functions let you pull out parts of a date, round dates to a month or week, and measure the time between two events. Almost every business report is organised by time, so these are must-know functions for analysts.

The examples use PostgreSQL. Function names differ between databases more for dates than for anything else; the FAQ lists the common MySQL and SQL Server equivalents.

Quick reference

Examples

1. Extract parts of a date

SELECT order_id,
       order_date,
       EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(QUARTER FROM order_date) AS order_qtr
FROM orders
LIMIT 4;

Result:

(4 rows)

2. Day of the week

EXTRACT(DOW ...) returns 0 for Sunday through 6 for Saturday. TO_CHAR gives the name (the FM prefix removes padding spaces):

SELECT order_id,
       order_date,
       EXTRACT(DOW FROM order_date) AS dow_number,
       TO_CHAR(order_date, 'FMDay') AS day_name
FROM orders
LIMIT 4;

Result:

(4 rows)

3. Days to deliver

Subtracting two dates gives a whole number of days:

SELECT order_id,
       order_date,
       delivery_date,
       delivery_date - order_date AS days_to_deliver
FROM orders
WHERE status = 'Delivered'
ORDER BY days_to_deliver DESC
LIMIT 5;

Result:

(5 rows)

4. Monthly order counts with DATE_TRUNC

DATE_TRUNC('month', ...) maps every date to the first of its month, so you can group by it:

SELECT DATE_TRUNC('month', order_date)::date AS order_month,
       COUNT(*) AS orders
FROM orders
WHERE order_date < '2025-07-01'
GROUP BY order_month
ORDER BY order_month;

Result:

(6 rows)

You'll learn GROUP BY properly in lesson 14. ::date drops the time part from the output.

5. Format dates for reports

SELECT order_id,
       TO_CHAR(order_date, 'DD Mon YYYY') AS order_date_label,
       TO_CHAR(order_date, 'YYYY-MM') AS year_month
FROM orders
LIMIT 3;

Result:

(3 rows)

6. Add and subtract intervals

A 7-day return window from the delivery date:

SELECT order_id,
       delivery_date,
       (delivery_date + INTERVAL '7 days')::date AS return_window_ends
FROM orders
WHERE status = 'Delivered'
LIMIT 3;

Result:

(3 rows)

7. Relative to today

Queries using CURRENT_DATE give different results every day, so their output isn't shown here. Try them in the compiler:

-- Orders placed in the last 30 days
SELECT order_id, order_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- How long each customer has been with ShopKart
SELECT customer_name, AGE(CURRENT_DATE, signup_date) AS tenure
FROM customers;

Common mistakes

• Text that looks like a date. '05/01/2025' is ambiguous (5 January or 1 May?). Always use 'YYYY-MM-DD'.
• Grouping by EXTRACT(MONTH ...) across years. January 2025 and January 2026 get merged. Use DATE_TRUNC('month', ...) or group by year and month together.
• BETWEEN on timestamps. Use >= start AND < next_day so you don't miss events late on the last day.

Practice question

Count how many orders ShopKart received in each quarter of 2025.

SELECT EXTRACT(QUARTER FROM order_date) AS quarter,
       COUNT(*) AS orders
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY quarter
ORDER BY quarter;

Key takeaways

• EXTRACT pulls out year, month, quarter, day of week and more.
• DATE_TRUNC rounds dates down and is the best way to group by month or week.
• Subtracting dates gives days; INTERVAL adds or removes time.
• TO_CHAR formats dates as readable text.