SQL Date and Time Functions

Concept

Date handling is where SQL dialects diverge most. The concepts are identical everywhere - now, add an interval, difference, extract a part, format - but almost every function name differs.

Syntax

EXTRACT(part FROM date) is the one form that is standard and works nearly everywhere. Prefer it.

Example

-- MySQL / MariaDB
SELECT first_name,
       hire_date,
       EXTRACT(YEAR FROM hire_date)              AS hire_year,
       DATEDIFF(CURDATE(), hire_date)            AS days_served,
       TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_served,
       DATE_ADD(hire_date, INTERVAL 1 YEAR)      AS first_review_due,
       DATE_FORMAT(hire_date, '%d %b %Y')        AS pretty_date
FROM   employees
ORDER BY hire_date;

-- Orders in the last 90 days, written so the index still works
SELECT id, order_date, total
FROM   orders
WHERE  order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
ORDER BY order_date DESC;

-- Monthly totals, grouped by the first day of each month
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start,
       COUNT(*)                            AS orders,
       SUM(total)                          AS revenue
FROM   orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')
ORDER BY month_start;

Explanation

Note the difference between the two date filters. The 90 day filter applies the function to CURDATE() - a constant - and leaves order_date bare, so an index on order_date can be used. Writing WHERE DATEDIFF(CURDATE(), order_date) <= 90 would compute a value for every row in the table instead.

TIMESTAMPDIFF(YEAR, ...) counts whole years, which is what "years of service" and "age" actually mean. Subtracting the year parts is wrong for anyone whose anniversary has not arrived yet this year.

Important rules

• Store dates as date types. Sorting and interval arithmetic on text is broken.
• A DATE has no time; a DATETIME/TIMESTAMP does. Comparing them mixes midnight into your logic.
• In MySQL, TIMESTAMP converts to and from the session time zone; DATETIME does not. That single sentence explains most "the report is off by one day" bugs.
• Week numbering, first day of week and quarter boundaries differ by product and by configuration.
• Keep the column bare in the WHERE clause and apply functions to the constant instead.

Common mistakes

• WHERE YEAR(order_date) = 2024 - correct answer, full table scan.
• BETWEEN '2024-01-01' AND '2024-01-31' on a DATETIME, losing everything after midnight on the 31st.
• Mixing UTC storage with local time comparisons and drifting by hours.
• Computing age as YEAR(now) - YEAR(dob).

Best practices

• Store timestamps in UTC; convert at the presentation edge.
• Use EXTRACT where a standard form exists; isolate the rest of the date logic in views or a small set of well named helpers.
• Filter with half open ranges built from constants.
• For "per month" reporting, group by a truncated date rather than by a formatted string where the engine offers it (DATE_TRUNC in PostgreSQL, DATETRUNC in SQL Server 2022+).

Practice

1. List employees who completed more than three years of service.
2. Return orders placed in the current calendar year, written so an index on order_date can be used.
3. Produce a month by month order count for 2024, including the month name.