SQL Date Range Bug - BETWEEN Missed 1,847 Orders

Every serious application tracks time. An e-commerce site needs to know which orders came in today. A SaaS dashboard needs to show signups per week. A payroll system needs to calculate how many days an employee has worked. Dates and times aren't a niche feature of SQL — they're woven into almost every real query that drives a business decision. If you can't slice and dice timestamps, you can't build useful reports, and your application logic leaks into places it doesn't belong.

The problem SQL date functions solve is deceptively simple: raw timestamps are stored as a single value (like '2024-03-15 09:42:11'), but the questions we ask about time are rich and varied. We want to group by month, calculate age in days, find records between two dates, or strip out the time portion to compare only dates. Without built-in functions, you'd either pull millions of rows into your app and filter in code — which is slow and wasteful — or write complicated string manipulation that breaks the moment a format changes.

By the end of this article you'll know how to get the current date and time from the database, calculate differences between dates, format timestamps for display, filter records by date ranges correctly, and truncate dates for grouping in reports. You'll also understand why each function exists and when to reach for it — not just what its syntax looks like.

Why SQL Date Functions Are the Root of a $1M Reporting Bug

SQL date/time functions let you manipulate, compare, and extract parts of temporal data — but they're not magic. The core mechanic is that every date/time value is stored as an internal numeric representation (e.g., days since epoch, seconds since 1970-01-01). Functions like DATE(), EXTRACT(), or DATE_TRUNC() convert, truncate, or decompose these values. The trap: implicit type coercion and timezone handling silently change what you're comparing.

In practice, BETWEEN is inclusive on both ends, so '2023-01-01' BETWEEN '2023-01-01' AND '2023-01-02' returns true. But if your column is a TIMESTAMP with a time component, '2023-01-01 00:00:00' is not the same as '2023-01-01'. The query WHERE order_date BETWEEN '2023-01-01' AND '2023-01-02' misses orders placed on 2023-01-02 at any time after midnight because the upper bound is midnight of that day, not the end of the day.

Use date functions when you need to group by day, filter by month, or join on date parts. But never use BETWEEN with TIMESTAMP columns for date-range filtering — it's a bug factory. Instead, use >= start_date AND < end_date + INTERVAL '1 day' to get a true day range. This pattern prevents off-by-one errors that silently drop thousands of rows.

Getting the Current Date and Time — Your Database's Internal Clock

Every database engine has a built-in clock you can query. This sounds trivial, but it's one of the most important features you'll use. Why? Because it means you never have to pass 'today's date' in from your application. The database knows what time it is, and using its clock keeps your data consistent even if records are inserted by multiple services running in different time zones or on different servers.

In MySQL and MariaDB, NOW() returns the full datetime at the moment the query starts. CURDATE() gives you just the date portion, and CURTIME() gives you just the time. In PostgreSQL, CURRENT_TIMESTAMP and NOW() both work, while CURRENT_DATE and CURRENT_TIME give the split versions. SQL Server uses GETDATE() for the current datetime and CAST(GETDATE() AS DATE) to strip the time.

A critical distinction: NOW() and CURRENT_TIMESTAMP capture the time once when the query begins. If you're inserting a million rows in a loop, every row gets the same timestamp — which is usually what you want for audit purposes. Functions like SYSDATE() in Oracle (or MySQL's SYSDATE()) re-evaluate on every row, which can cause subtle inconsistencies in bulk operations. Stick with NOW() unless you explicitly need per-row timing.

-- ============================================================
-- Demonstrates retrieving the current date and time in MySQL.
-- Run this to understand what each function actually returns.
-- ============================================================

-- Full timestamp: date + time together
SELECT NOW() AS current_datetime;          -- e.g. 2024-03-15 09:42:11

-- Date only — no time portion
SELECT CURDATE() AS today_date;            -- e.g. 2024-03-15

-- Time only — no date portion
SELECT CURTIME() AS current_time;         -- e.g. 09:42:11

-- Practical use: stamping a new user registration
-- The created_at column is set to RIGHT NOW automatically
INSERT INTO user_accounts (username, email, created_at)
VALUES ('sarah_jones', 'sarah@example.com', NOW());

-- Practical use: find all users who registered today
-- CURDATE() strips the time so we compare apples to apples
SELECT username, email, created_at
FROM   user_accounts
WHERE  DATE(created_at) = CURDATE();  -- DATE() strips time from the stored timestamp

Calculating Date Differences — How Long Ago, How Many Days Left

Once you can get the current date, the next question is always 'how far away is this other date?' This powers subscription expiry logic, overdue invoice detection, customer churn analysis, and age calculations. The function for this varies by database, but the concept is universal.

In MySQL, DATEDIFF(end_date, start_date) returns the number of days between two dates. It only counts whole days — it ignores the time component. For more granular differences (hours, minutes, seconds), you use TIMESTAMPDIFF(unit, start, end) where unit can be SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR. PostgreSQL handles this more elegantly with the subtraction operator: 'end_date - start_date' returns an INTERVAL, and you extract the part you want. SQL Server uses DATEDIFF(unit, start_date, end_date) — note the argument order is reversed compared to MySQL, which is a classic gotcha.

Real-world pattern: a subscription platform needs to flag accounts where the trial expires within 7 days. You calculate the difference between the expiry date and today, then filter where that result is between 0 and 7. You'd run this as a scheduled daily query to feed a notification queue. This kind of date math is core to keeping any recurring-revenue business healthy.

-- ============================================================
-- Real-world subscription expiry and overdue invoice queries.
-- Uses MySQL syntax. PostgreSQL notes included in comments.
-- ============================================================

-- Sample data context:
-- subscriptions table: id, customer_name, trial_start, trial_end
-- invoices table: id, customer_id, amount_due, due_date, paid_at

-- -------------------------------------------------------
-- 1. Find trials expiring in the next 7 days (send a reminder)
-- -------------------------------------------------------
SELECT
    customer_name,
    trial_end,
    DATEDIFF(trial_end, CURDATE()) AS days_remaining  -- positive = future, 0 = today, negative = already expired
FROM subscriptions
WHERE DATEDIFF(trial_end, CURDATE()) BETWEEN 0 AND 7
ORDER BY days_remaining ASC;  -- most urgent first

-- -------------------------------------------------------
-- 2. Find overdue invoices and how many days overdue they are
-- -------------------------------------------------------
SELECT
    i.id                          AS invoice_id,
    c.company_name,
    i.amount_due,
    i.due_date,
    DATEDIFF(CURDATE(), i.due_date) AS days_overdue  -- note argument order: (later, earlier) = positive number
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.paid_at IS NULL                             -- not yet paid
  AND i.due_date < CURDATE()                        -- past the due date
ORDER BY days_overdue DESC;                         -- worst offenders first

-- -------------------------------------------------------
-- 3. Calculate customer account age in years and months
-- TIMESTAMPDIFF lets you choose the unit of measurement
-- -------------------------------------------------------
SELECT
    username,
    created_at,
    TIMESTAMPDIFF(YEAR,  created_at, NOW()) AS account_age_years,
    TIMESTAMPDIFF(MONTH, created_at, NOW()) AS account_age_months  -- total months, not just the leftover
FROM user_accounts
ORDER BY created_at ASC;

-- PostgreSQL equivalent for days_overdue (uses interval subtraction):
-- SELECT id, CURRENT_DATE - due_date AS days_overdue FROM invoices WHERE paid_at IS NULL;

Extracting and Truncating Dates — The Secret to Clean Reports

Reporting is where date functions earn their keep. When a product manager asks 'show me signups per month for the last year', your raw created_at column has thousands of unique timestamps — one per user. You need to collapse them into monthly buckets. That's what extraction and truncation are for.

EXTRACT(part FROM date) — available in MySQL, PostgreSQL, and SQL Server (as DATEPART) — pulls out a single component: the year, month, day, hour, etc. It returns a number, which makes it perfect for GROUP BY clauses. DATE_TRUNC('month', timestamp) in PostgreSQL (and DATE_FORMAT in MySQL) rounds a timestamp down to the start of a period — so '2024-03-15 09:42:11' becomes '2024-03-01 00:00:00'. This is more powerful for grouping because you keep a valid date value instead of just a number, which means your charting tools and ORDER BY clauses work correctly without extra manipulation.

In MySQL, there's no DATE_TRUNC, so the idiomatic equivalent is DATE_FORMAT(created_at, '%Y-%m-01') — format the date but hardcode the day as 01. It's a bit of a hack but it's universally used in MySQL shops. Understanding both approaches makes you fluent across database engines, which interviewers love.

-- ============================================================
-- Monthly signup report and weekly revenue aggregation.
-- Shows both MySQL and PostgreSQL approaches side by side.
-- ============================================================

-- -------------------------------------------------------
-- MySQL: Signups grouped by month (last 12 months)
-- DATE_FORMAT with '%Y-%m-01' normalises all days to the 1st
-- so every row in a calendar month gets the same bucket key
-- -------------------------------------------------------
SELECT
    DATE_FORMAT(created_at, '%Y-%m-01')   AS month_start,    -- '2024-02-01', '2024-03-01', etc.
    DATE_FORMAT(created_at, '%M %Y')      AS month_label,    -- 'February 2024' — human-friendly for reports
    COUNT(*)                              AS new_signups,
    SUM(CASE WHEN plan_type = 'paid' THEN 1 ELSE 0 END) AS paid_signups  -- break out paid vs free
FROM user_accounts
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)  -- rolling 12-month window
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01')                -- group by the normalised bucket
ORDER BY month_start ASC;                                   -- chronological order

-- -------------------------------------------------------
-- PostgreSQL equivalent using DATE_TRUNC (cleaner syntax)
-- DATE_TRUNC rounds DOWN to the start of the given period
-- -------------------------------------------------------
-- SELECT
--     DATE_TRUNC('month', created_at) AS month_start,
--     COUNT(*) AS new_signups
-- FROM user_accounts
-- WHERE created_at >= NOW() - INTERVAL '12 months'
-- GROUP BY DATE_TRUNC('month', created_at)
-- ORDER BY month_start ASC;

-- -------------------------------------------------------
-- EXTRACT: Pull individual components for pivot-style reports
-- Useful when you want day-of-week or hour-of-day analysis
-- -------------------------------------------------------
SELECT
    EXTRACT(HOUR FROM order_placed_at)  AS hour_of_day,     -- 0–23
    DAYNAME(order_placed_at)            AS day_name,         -- 'Monday', 'Tuesday', etc. (MySQL)
    COUNT(*)                            AS total_orders,
    ROUND(AVG(order_total), 2)          AS avg_order_value
FROM orders
WHERE order_placed_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY EXTRACT(HOUR FROM order_placed_at), DAYNAME(order_placed_at)
ORDER BY total_orders DESC
LIMIT 10;  -- top 10 busiest hour/day combinations

Filtering by Date Ranges — The Right Way to Query Time Windows

Filtering by date is where most SQL bugs in production originate. The query looks right, the logic sounds right, but the results are subtly wrong — usually because of how datetime precision interacts with comparison operators.

The safest, most portable pattern for filtering a date range is a half-open interval: WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'. This includes every record from the first millisecond of March through the last moment of March, without accidentally including midnight on April 1st. Using BETWEEN with a full date like '2024-03-31' is dangerous because BETWEEN is inclusive — on a datetime column it only captures records up to '2024-03-31 00:00:00', silently missing everything from 00:00:01 onwards.

For dynamic rolling windows — 'the last 30 days', 'the last 7 days' — use DATE_SUB in MySQL or interval arithmetic in PostgreSQL. The key insight is to keep your filter on the raw column, not on a function applied to it. Writing WHERE DATE(created_at) = CURDATE() forces a full table scan because the database can't use an index on a transformed column. Writing WHERE created_at >= CURDATE() AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY) keeps the left side clean and lets your index do its job.

-- ============================================================
-- Safe, index-friendly date range filtering patterns.
-- These patterns work across MySQL, PostgreSQL, and SQL Server.
-- ============================================================

-- -------------------------------------------------------
-- PATTERN 1: Specific calendar month — use half-open interval
-- BETWEEN '2024-03-01' AND '2024-03-31' MISSES the last 23h 59m 59s
-- This pattern captures every row in March, no matter the time
-- -------------------------------------------------------
SELECT
    order_id,
    customer_id,
    order_total,
    order_placed_at
FROM orders
WHERE order_placed_at >= '2024-03-01 00:00:00'   -- first moment of March
  AND order_placed_at <  '2024-04-01 00:00:00'   -- stops BEFORE April (half-open)
ORDER BY order_placed_at DESC;

-- -------------------------------------------------------
-- PATTERN 2: Rolling window — last 30 days from right now
-- DATE_SUB dynamically calculates the window start
-- Index-friendly because the column is untouched on the left side
-- -------------------------------------------------------
SELECT
    customer_id,
    COUNT(*)          AS orders_last_30_days,
    SUM(order_total)  AS revenue_last_30_days
FROM orders
WHERE order_placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)  -- 30 days ago to now
GROUP BY customer_id
HAVING COUNT(*) >= 3  -- customers with at least 3 orders — loyalty segment
ORDER BY revenue_last_30_days DESC;

-- -------------------------------------------------------
-- PATTERN 3: Today only — the right and WRONG way
-- WRONG (breaks index): WHERE DATE(created_at) = CURDATE()
-- RIGHT (index-friendly): use explicit range
-- -------------------------------------------------------

-- RIGHT WAY — the database can use an index on created_at
SELECT COUNT(*) AS signups_today
FROM user_accounts
WHERE created_at >= CURDATE()                              -- start of today (midnight)
  AND created_at <  DATE_ADD(CURDATE(), INTERVAL 1 DAY);  -- start of tomorrow

-- -------------------------------------------------------
-- PATTERN 4: Year-to-date (Jan 1 of current year to now)
-- -------------------------------------------------------
SELECT
    MONTHNAME(order_placed_at)   AS month_name,
    COUNT(*)                     AS total_orders,
    SUM(order_total)             AS total_revenue
FROM orders
WHERE order_placed_at >= DATE_FORMAT(CURDATE(), '%Y-01-01')  -- Jan 1 of current year
  AND order_placed_at <  NOW()                               -- up to this moment
GROUP BY MONTH(order_placed_at), MONTHNAME(order_placed_at)
ORDER BY MONTH(order_placed_at) ASC;

Date Arithmetic with INTERVAL — Stop Writing Wrong Queries for 'Last Month'

You've seen it a hundred times: WHERE placed_at >= NOW() - INTERVAL 30 DAY. Works until months with 31 days break your report. Then someone 'fixes' it with DATE_SUB(NOW(), INTERVAL 1 MONTH). That's better. But do you know why?

INTERVAL isn't magic syntax sugar. It's the database telling you: 'I will handle calendar math correctly.' Adding 1 month to January 31st doesn't give you February 31st — the engine knows February has 28 or 29 days. It clamps to the last valid day. That's the entire point.

The mistake? Using INTERVAL with CURRENT_DATE when you meant NOW(). CURRENT_DATE strips time. So CURRENT_DATE - INTERVAL 1 DAY gives you midnight of yesterday, not '24 hours ago.' If your report runs at 3 PM, you just excluded 15 hours of data. Use NOW() for rolling windows, CURRENT_DATE for calendar-day boundaries.

And for the love of god, don't do WHERE DATEDIFF(NOW(), placed_at) < 30. That's a full table scan every time. Use a sargable filter with INTERVAL and an index on placed_at.

// io.thecodeforge — database tutorial

-- Correct: sargable, uses index on placed_at
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days'
  AND placed_at < NOW();

-- Wrong: full scan, breaks on month boundaries
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATEDIFF(NOW(), placed_at) < 30;

-- Never: assumes 30 days is 'last month'
SELECT order_id, placed_at, total_amount
FROM orders
WHERE EXTRACT(MONTH FROM placed_at) = EXTRACT(MONTH FROM NOW()) - 1;

-- Correct calendar month: use INTERVAL 1 MONTH
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at >= DATE_TRUNC('month', NOW()) - INTERVAL '1 month'
  AND placed_at < DATE_TRUNC('month', NOW());

Extracting Parts of a Date — When You Need the Month, Not the Moment

Reporting by quarter? Grouping revenue by month? You need to rip the date apart. EXTRACT is your standard SQL tool for this. EXTRACT(YEAR FROM placed_at) returns 2024. EXTRACT(MONTH FROM placed_at) returns 5 for May. It's clean, portable, and works everywhere.

But here's where PostgreSQL flexes: DATE_PART('month', placed_at) does the same thing. Why two functions for the same job? Legacy. DATE_PART is older, EXTRACT is standard. Use EXTRACT. Your future self won't have to explain to a MySQL dev what DATE_PART does.

MySQL people get MONTH(placed_at), YEAR(placed_at), DAY(placed_at). Shorter, yes. But they also get DATE_FORMAT for string output, which PostgreSQL handles with TO_CHAR. The portability cost is real.

The rookie mistake? Grouping by EXTRACT(MONTH FROM placed_at) but forgetting to also group by the year. Congratulations, your January 2023 and January 2024 revenue are now merged into one bucket. Always extract year and month together, or use DATE_TRUNC('month', placed_at) which preserves the timestamp type and sorts correctly across year boundaries.

// io.thecodeforge — database tutorial

-- WRONG: no year → merges January across years
SELECT
  EXTRACT(MONTH FROM placed_at) AS month_num,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM placed_at);

-- CORRECT: group by truncated month
SELECT
  DATE_TRUNC('month', placed_at) AS month_start,
  SUM(total_amount) AS revenue
FROM orders
WHERE placed_at >= '2024-01-01'
  AND placed_at < '2025-01-01'
GROUP BY DATE_TRUNC('month', placed_at)
ORDER BY month_start;

8. DATE_FORMAT() — Why Your Reports Look Like Timestamps Instead of Dates

Raw timestamps are for machines. Humans need 'Mar 15, 2024' or '2024-Q1'. DATE_FORMAT() is the only portable way to transform a datetime into any string shape your business requires. Understand WHY: CAST(date AS VARCHAR) is database-specific and loses timezone awareness. DATE_FORMAT() gives you explicit control over each component—month name, zero-padded day, 12-hour clock—and works identically across MySQL, MariaDB, and many other SQL engines. The first argument is your date column; the second is a format string built from specifiers like %Y (four-digit year), %b (abbreviated month), and %H (24-hour hour). Missing this function forces you to write convoluted CONCAT expressions that break when daylight saving time shifts. Use DATE_FORMAT(billing_date, '%Y-%m-%d') for ISO compliance, or DATE_FORMAT(created_at, '%M %D, %Y') for executive summaries.

// io.thecodeforge — database tutorial

SELECT
  id,
  DATE_FORMAT(submitted_at, '%W, %M %e, %Y') AS human_date
FROM orders
WHERE status = 'shipped'
LIMIT 5;

9. Datatypes — A Date Column That Is Not a Date Will Break Every Function You Write

Every SQL date function—DATEDIFF, DATE_ADD, EXTRACT—silently depends on the underlying column being a true date/datetime type. WHY this matters: many legacy systems store '2024-03-15' as a VARCHAR or TEXT. These strings pass equality checks but fail on comparisons (December 9 > January 10? No, '12' < '2' lexicographically). Sorting breaks. INTERVAL arithmetic throws errors or returns garbage. The three standard temporal types are DATE (no time), DATETIME (date + time), and TIMESTAMP (timezone-aware, usually stored as UTC). Always verify your schema with SHOW COLUMNS or INFORMATION_SCHEMA.COLUMNS to confirm the datatype before writing any filtering logic. If you inherit a VARCHAR date column, use STR_TO_DATE() to cast it to a proper type before applying date functions—otherwise your 'saved $1M' query is a time bomb.

// io.thecodeforge — database tutorial

SELECT
  order_date_raw,
  STR_TO_DATE(order_date_raw, '%Y-%m-%d') AS proper_date
FROM orders
WHERE STR_TO_DATE(order_date_raw, '%Y-%m-%d') > '2024-01-01'
LIMIT 3;