“Sales this month”, “orders in the last 30 days”, “average days to deliver”: almost every business question involves dates. These are the SQL date techniques analysts use every week. Function names differ slightly between databases, so examples show MySQL with PostgreSQL notes.
Extracting parts of a date
SELECT YEAR(order_date), MONTH(order_date), DAYNAME(order_date) FROM orders;
In PostgreSQL use EXTRACT(YEAR FROM order_date) and TO_CHAR(order_date, 'Day'). Weekday analysis often reveals useful patterns, such as which days bring the most enquiries.
Grouping by month
Group by a month start date rather than by month number, so January 2025 and January 2026 do not merge:
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month, SUM(amount)
FROM orders GROUP BY month ORDER BY month;
In PostgreSQL: DATE_TRUNC('month', order_date).
Differences between dates
SELECT AVG(DATEDIFF(delivered_date, order_date)) AS avg_days_to_deliver FROM orders;
In PostgreSQL, subtracting two dates gives the number of days directly.
Filtering date ranges correctly
Last 30 days: WHERE order_date >= CURDATE() - INTERVAL 30 DAY.
For a whole month, use a half-open range: WHERE order_date >= '2026-11-01' AND order_date < '2026-12-01'. This avoids missing orders from the last day when the column also stores a time, which is a common bug with BETWEEN.
Performance tip
Avoid wrapping the column in a function inside WHERE, such as WHERE YEAR(order_date) = 2026. It can stop the database using an index. Write the range form instead.
Practice questions
- Monthly revenue for the last 12 months.
- Orders per weekday.
- Customers whose last order was more than 90 days ago.
Related: SQL subqueries vs CTEs, SQL window functions and GROUP BY and HAVING.
SQL is taught with real datasets in our Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.

SQL Indexes Explained for Beginners - ScholarLogic
[…] SQL date functions for data analysts, SQL subqueries vs CTEs and SQL JOINs […]