SQL Date Filtering: WHERE, BETWEEN & DATE Functions Explained with Examples
Filtering by date is where beginners and pros diverge. Learn the exact WHERE clauses for today, yesterday, last 7 days, and current month across MySQL, PostgreSQL, and SQL Server.
Why Date Filtering Feels Hard
Dates look like strings, but they're not — and the database knows it. The three classic mistakes: comparing a DATETIME column to a DATE literal (loses the time part), filtering with BETWEEN on a timestamp (misses the last day), and using string functions instead of date functions (kills index usage).
Today's Records
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE; (PostgreSQL) — in MySQL: WHERE DATE(created_at) = CURDATE(). For SQL Server: WHERE CAST(created_at AS date) = CAST(GETDATE() AS date). The CAST/DATE wrapper is slightly index-unfriendly; on large tables prefer a range: created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL 1 DAY.
Last 7 Days (Including Today)
WHERE created_at >= CURRENT_DATE - INTERVAL 6 DAY — six days back plus today equals seven. Use INTERVAL 7 DAY only if you want exactly the last 7 full days.
Current Month
PostgreSQL: WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE). MySQL: WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01'). SQL Server: WHERE created_at >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1).
BETWEEN: The Traps
BETWEEN is inclusive on both ends — created_at BETWEEN '2026-08-01' AND '2026-08-31' misses everything on Aug 31 after midnight because a DATETIME literal defaults to 00:00:00. Fix: use >= start AND < end (exclusive end) or add the full time: AND '2026-08-31 23:59:59'. The exclusive-end pattern is the professional standard — it's also correct for timestamps with milliseconds.
Dynamic Dates Without Hardcoding
Last month: created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL 1 MONTH) AND created_at < DATE_TRUNC('month', CURRENT_DATE). Yesterday: created_at >= CURRENT_DATE - INTERVAL 1 DAY AND created_at < CURRENT_DATE. These patterns read as intent, survive year boundaries, and don't need a scheduled job to update.
Performance Note
Wrapping a column in a function (DATE(created_at)) prevents index use. On big tables, always prefer the range form on the raw column — the optimizer can use an index on created_at directly. If you filter by day constantly, consider a generated column created_date and index it.