Working with dates

Filter, group, shift and measure dates, and avoid the timestamp traps that make month-end numbers wrong.

Date questions are everywhere in reporting: this month’s pipeline, deals per quarter, days to close. Every database handles dates a little differently, so this lesson teaches the ideas with the sandbox’s syntax, then maps each one to the other databases at the end.

How dates look

Always write dates as 'YYYY-MM-DD'. Every major database reads that format, and it sorts correctly even as text.

Today’s date is CURRENT_DATE on most databases, including the sandbox.

The timestamp trap

Exactly one deal closed on April 2, 2026. Watch what happens when you ask for it:

SELECT COUNT(*) FROM opportunities WHERE closed_at = '2026-04-02';          -- 0

closed_at holds 2026-04-02 10:45:00. A date on its own means the very start of that day, midnight, so only a deal closed at exactly 00:00:00 would match. Two fixes:

-- 1. A range for the whole day. Works everywhere, and is the fastest on big tables.
WHERE closed_at >= '2026-04-02'
  AND closed_at <  '2026-04-03'
 
-- 2. Keep just the date part, then compare.
WHERE date(closed_at) = '2026-04-02'

This is the date version of lesson 4’s half-open range, and it’s why a “deals closed on the last day of the quarter” report can come back empty.

Pulling out parts: year, month, weekday

In the sandbox, strftime(format, date) returns any part of a date as text:

SELECT strftime('%Y', '2026-03-15'),      -- '2026'
       strftime('%m', '2026-03-15'),      -- '03'
       strftime('%Y-%m', '2026-03-15');   -- '2026-03'

Grouping by month

This is the most common date query there is. Turn each date into its month, then group:

SELECT strftime('%Y-%m', created_at) AS month,
       COUNT(*) AS deals
FROM opportunities
GROUP BY month
ORDER BY month;

Use year and month (%Y-%m), not just %m. Grouping by month alone puts March 2025 and March 2026 in the same group.

Moving a date forward or back

SELECT date('2026-03-15', '+30 days'),          -- 2026-04-14
       date('2026-03-15', '-1 year'),           -- 2025-03-15
       date('2026-03-15', 'start of month');    -- 2026-03-01

'start of month' snaps a date back to the first of its month, which is handy for “this month so far” filters.

Watch month ends: adding a month to January 31 has no clean answer, because February has no 31st. The sandbox overflows into March: date('2026-01-31', '+1 month') gives 2026-03-03. Most other databases give February 28. If month-end dates matter, snap to the start of the month first, then add.

Measuring time between dates

In the sandbox, julianday() turns a date into a day count, so subtracting two of them gives the days between:

SELECT opportunity_id,
       julianday(closed_at) - julianday(created_at)        AS days_with_time,
       julianday(date(closed_at)) - julianday(created_at)  AS whole_days
FROM opportunities
WHERE closed_at IS NOT NULL;

Deal 1001 was created on 2025-01-24 and closed at 2025-03-24 16:00:00. That’s 59.67 days with the time included, or 59 whole days. Decide which one the report needs. “Days to close” usually means whole days.

Common mistakes

Dialect notes

The ideas are the same everywhere. The function names aren’t:

Watch the argument order for days between: SQL Server puts the start first, MySQL puts the end first. Swap them and every result flips sign.

Snowflake notes

DATEDIFF counts boundaries crossed, not time elapsed: DATEDIFF('day', '2026-10-31 23:59', '2026-11-01 00:01') is 1, though only two minutes passed. For hours or response times, measure in minutes or seconds and divide.

Snowflake has three timestamp types. TIMESTAMP_NTZ stores no time zone, TIMESTAMP_LTZ shows each viewer their own zone, and TIMESTAMP_TZ keeps the original offset. CONVERT_TIMEZONE('UTC', 'America/Chicago', ts) converts for display.

More in the field guide: Date functions, Dates and timestamps and Elapsed time. Exercise T2 is the timestamp trap in a real report.

Try it

How many deals were created in each month of 2026, and what were they worth? Return the month as YYYY-MM, the number of deals and their total amount, in month order.

Stretch: what’s the average number of whole days to close for each lead source? That’s field guide exercise I2, written for Snowflake. Rewrite it with the sandbox’s date functions.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson