SQL Date Functions: DATEDIFF, DATE_FORMAT, EXTRACT & Date Math

sql date functions split into four families that every data engineering query eventually touches: type conversion (DATE, TIMESTAMP, TIMESTAMPTZ), difference (datediff sql and friends — the sql datediff / mysql datediff / sql server datediff cluster), extraction (EXTRACT, DATE_PART, DATE_TRUNC), and sql date format for converting dates back into strings (mysql date format / mysql date_format / format date sql). Add date arithmetic with INTERVAL and DATE_ADD and you can answer almost every time-dimension sql interview questions prompt in a panel — from "orders in the last 7 days" to "monthly revenue rollups" to "age in years from a birthdate column."

This guide walks through every clause in the date-function family that reviewers test in data engineering interview questions: the full sql date type hierarchy (DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL), the dialect jungle around datediff in sql (Postgres has no built-in; MySQL takes two args; SQL Server takes three), date format in sql patterns for dd/mm/yyyy and yyyy-mm-dd, extract in sql for pulling year/month/day, date_trunc sql for rounding to period boundaries, and the date-arithmetic patterns (interval sql postgres, DATE_ADD, DATEADD) every sql for data engineers loop tests. Every section ends as sql interview questions with answers: a runnable PostgreSQL query, a traced execution, an output table, and a concept-by-concept why this works breakdown.

When you want hands-on reps immediately after reading, browse date-functions SQL practice →, drill the date-arithmetic lane →, sharpen time-series SQL drills →, rehearse aggregation patterns with DATE_TRUNC →, or widen coverage on the full SQL practice library →.

---

On this page

• Why SQL date functions matter in data engineering interviews
• SQL date types — DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
• DATEDIFF and date difference — across SQL Server, MySQL, Postgres, Snowflake
• DATE_FORMAT, TO_CHAR, and FORMAT — turning dates into strings
• EXTRACT and DATE_PART — pulling year, month, day, hour, dow
• DATE_TRUNC — rounding to month, week, day, hour boundaries
• Date arithmetic with INTERVAL, DATE_ADD, and DATEADD
• Common date interview patterns — BETWEEN, rolling windows, age
• Choosing the right date function (cheat sheet)
• Frequently asked questions
• Practice on PipeCode

---

1. Why SQL date functions matter in data engineering interviews

The four date function families plus arithmetic — that's the whole interview surface

The one-sentence invariant: sql date functions split into four families — type conversion (DATE, TIMESTAMP, TIMESTAMPTZ), difference (DATEDIFF, AGE, direct subtraction), extraction (EXTRACT, DATE_PART, DATE_TRUNC), and formatting (DATE_FORMAT, TO_CHAR, FORMAT) — plus arithmetic with INTERVAL to add or subtract durations. Once you internalise that taxonomy, every date-related interview prompt collapses to "pick the right function from the right family."

The four function families.

• Type conversion — CAST(x AS DATE), TO_TIMESTAMP(string, format), DATE(timestamp) — convert between strings, dates, and timestamps.
• Difference — datediff sql (across dialects), AGE() (Postgres), MONTHS_BETWEEN() (Oracle), direct subtraction d1 - d2.
• Extraction — pull components: EXTRACT(YEAR FROM d), DATE_PART('month', d), YEAR(d), MONTH(d), plus DATE_TRUNC() to round to a period boundary.
• Formatting — sql date format patterns: DATE_FORMAT(d, '%Y-%m-%d') (MySQL), TO_CHAR(d, 'YYYY-MM-DD') (Postgres / Oracle), FORMAT(d, 'yyyy-MM-dd') (SQL Server).

Why interviewers love date functions.

• Every business question has a time dimension — sales by month, signups by week, sessions in the last 30 days, age at signup, time-to-first-purchase.
• Dialect divergence is huge — datediff in sql looks completely different across PostgreSQL, MySQL, SQL Server, and Snowflake; reviewers test whether you've worked across engines.
• Time zones are a senior-signal pitfall — TIMESTAMP vs TIMESTAMPTZ, AT TIME ZONE, DST boundaries; getting these wrong silently shifts the answer by hours.
• Performance traps are common — wrapping an indexed order_date in EXTRACT(YEAR FROM order_date) defeats the index; date-range comparisons are the fix.

What interviewers listen for.

• Do you name the family for the function you're picking? — "this is an extraction, so I'll reach for EXTRACT".
• Do you mention dialect differences when reaching for datediff? — senior signal.
• Do you use half-open intervals (>= start AND < next-period-start) instead of inclusive BETWEEN? — avoids the midnight boundary trap.
• Do you flag TIMESTAMP vs TIMESTAMPTZ when time zones could matter? — bonus points.

Worked example — a single query that uses all four families

Question. From orders(order_id, customer_id, order_ts, amount), return per customer for orders in the last 7 days: the formatted last order date, days since last order, and the order-month name.

Input. orders slice.

order_id	customer_id	order_ts	amount
1	42	2026-05-18 14:30	100
2	42	2026-05-20 09:15	150
3	17	2026-05-21 18:45	80

Code (PostgreSQL).

SELECT customer_id,
       TO_CHAR(MAX(order_ts), 'YYYY-MM-DD')         AS last_order_iso,        -- formatting family
       CURRENT_DATE - MAX(order_ts)::date           AS days_since_last_order, -- difference family
       EXTRACT(MONTH FROM MAX(order_ts))            AS month_number,          -- extraction family
       MAX(order_ts)::date                          AS last_order_date        -- type-conversion family
FROM orders
WHERE order_ts >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY customer_id
ORDER BY days_since_last_order;

Step-by-step explanation.

1. WHERE order_ts >= CURRENT_DATE - INTERVAL '7 days' filters to the rolling window (date arithmetic — family #5).
2. GROUP BY customer_id collapses to one row per customer.
3. TO_CHAR(MAX(order_ts), 'YYYY-MM-DD') formats the latest order timestamp as an ISO string (formatting family).
4. CURRENT_DATE - MAX(order_ts)::date computes the day difference (difference family).
5. EXTRACT(MONTH FROM MAX(order_ts)) pulls the month number (extraction family).
6. MAX(order_ts)::date casts the timestamp to a date (type-conversion family).

Output.

customer_id	last_order_iso	days_since_last_order	month_number	last_order_date
17	2026-05-21	1	5	2026-05-21
42	2026-05-20	2	5	2026-05-20

Rule of thumb: identify the family first, then pick the dialect's function — it's a far reliable mental model than memorising every function signature.

SQL
Topic — date-functions
Date functions SQL drills

Practice →

SQL
Topic — date-arithmetic
Date arithmetic SQL practice

Practice →

---

2. SQL date types — DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL

Pick the sql date type that matches the question — half the bugs are wrong-type bugs

Before any function call comes the question of which type the column should be. The sql date type family is small but the dialect names diverge — knowing postgresql timestamp vs MySQL DATETIME vs SQL Server DATETIME2 is what separates candidates who've shipped production code from candidates who've only read tutorials.

DATE — calendar day only.

• Stores — year-month-day (2026-05-22); no time, no time zone.
• Bytes — 3–4 typically.
• Use it for — birthdates, signup dates, calendar events that don't care about time-of-day.
• current date sql — CURRENT_DATE (ANSI), CURDATE() (MySQL), GETDATE()::date (SQL Server), getdate in sql returns the current date/time.

TIME — time of day only.

• Stores — hour-minute-second (14:30:00); no date.
• Rarely used in interviews — most queries use TIMESTAMP and EXTRACT(HOUR FROM …) when they need time-of-day.

TIMESTAMP / DATETIME — date + time, no time zone.

• Stores — 2026-05-22 14:30:00 as a "wall clock" value.
• PostgreSQL — TIMESTAMP is alias for TIMESTAMP WITHOUT TIME ZONE; 8 bytes.
• MySQL — DATETIME is the no-TZ type (8 bytes); TIMESTAMP is a separate type that quietly converts to UTC on insert and back to session TZ on read (4 bytes, UNIX-epoch range only).
• SQL Server — DATETIME (legacy, less precision) and DATETIME2 (recommended, microsecond precision).
• current timestamp in sql query — CURRENT_TIMESTAMP (ANSI), NOW() (MySQL / Postgres), GETDATE() (SQL Server), sql date now is the same idea.

TIMESTAMPTZ / TIMESTAMP WITH TIME ZONE — moment in time, stored UTC.

• PostgreSQL TIMESTAMPTZ — 8 bytes; stored as UTC internally; displayed in the session's TimeZone setting; arithmetic uses UTC under the hood (safe across DST).
• Snowflake TIMESTAMP_TZ — same idea.
• SQL Server DATETIMEOFFSET — stores an explicit offset alongside the timestamp.
• Use it for — multi-region applications where the same event must display correctly in every user's local time zone.
• The postgresql timestamp vs TIMESTAMPTZ distinction is the most-asked time-zone interview question.

INTERVAL — a duration, not a moment.

• PostgreSQL / Oracle / Snowflake / BigQuery — INTERVAL '7 days', INTERVAL '1 month 3 days', INTERVAL '02:30:00'.
• Use it for — date arithmetic: order_ts + INTERVAL '7 days', now() - INTERVAL '1 hour'.
• MySQL — uses INTERVAL N UNIT syntax inside DATE_ADD / DATE_SUB: DATE_ADD(d, INTERVAL 7 DAY).
• SQL Server — no INTERVAL literal; uses DATEADD(DAY, 7, d) instead.

"Now" functions per dialect — the cheat-bullet.

• PostgreSQL — CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, NOW(), LOCALTIMESTAMP, LOCALTIME.
• MySQL — CURDATE(), CURTIME(), NOW(), CURRENT_TIMESTAMP, CURRENT_DATE.
• SQL Server — GETDATE(), SYSDATETIME(), CURRENT_TIMESTAMP, SYSUTCDATETIME().
• Oracle — SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, SYSTIMESTAMP.
• Snowflake — CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), SYSDATE().
• to_timestamp in sql — Postgres TO_TIMESTAMP('2026-05-22 14:30', 'YYYY-MM-DD HH24:MI') converts a formatted string into a TIMESTAMPTZ.

Common type pitfalls.

• Storing dates as VARCHAR — sorts lexicographically, not chronologically; reaches for CAST everywhere; common rookie mistake.
• Using DATETIME when you meant TIMESTAMPTZ — silently shifts times across DST and across regions.
• Comparing DATE with TIMESTAMP — most engines auto-cast, but the cast direction can surprise you; explicit casts are safer.
• Ignoring the midnight boundary — WHERE order_ts BETWEEN '2026-05-01' AND '2026-05-31' excludes late-night 2026-05-31 timestamps because '2026-05-31' casts to 2026-05-31 00:00:00.

SQL
Topic — date-time
Date / time SQL problems

Practice →

SQL
Topic — date-functions
Date functions library

Practice →

---

3. DATEDIFF and date difference — across SQL Server, MySQL, Postgres, Snowflake

datediff sql is the most dialect-divergent function on the menu — know all four signatures

The sql date difference family is where dialect knowledge pays off the most. SQL Server has DATEDIFF(unit, start, end). MySQL has DATEDIFF(end, start) with no unit argument (days only). PostgreSQL has no built-in DATEDIFF at all — you subtract directly. Snowflake matches SQL Server. Knowing all four signatures is what separates candidates who've worked across engines from candidates who've only used one.

SQL Server DATEDIFF(unit, start, end) — three args.

• Signature — DATEDIFF(unit, start_date, end_date).
• Units — DAY, HOUR, MINUTE, SECOND, MONTH, QUARTER, YEAR, WEEK, etc.
• Returns — integer count of unit boundaries crossed (not full unit-lengths).
• The gotcha — DATEDIFF(YEAR, '2025-12-31', '2026-01-01') returns 1 even though only 1 day passed; one year-boundary was crossed.
• sql server datediff is universally tested in SQL Server / Azure SQL interviews.

MySQL DATEDIFF(end, start) — two args, days only.

• Signature — DATEDIFF(end_date, start_date) (note: end first).
• Returns — integer days between the two dates.
• mysql datediff does NOT take a unit argument; for other units use TIMESTAMPDIFF(unit, start, end) (note the reversed arg order vs SQL Server's DATEDIFF).
• The gotcha — argument order is end, start in DATEDIFF but start, end in TIMESTAMPDIFF; mixing them up is a classic bug.
• datediff mysql is the most common search variant of this same concept.

PostgreSQL — no built-in DATEDIFF; subtract directly.

• Direct subtraction — end_date - start_date returns an integer count of days when both sides are DATE.
• For TIMESTAMP columns — end_ts - start_ts returns an INTERVAL like '14 days 03:25:00'; use EXTRACT(EPOCH FROM …) / 86400 for fractional days.
• AGE(end_date, start_date) — returns an interval in years-months-days form; great for human-readable "age" outputs.
• datediff in sql on Postgres — the most common workaround is (end_date - start_date) for DATE columns or EXTRACT(DAY FROM (end_ts - start_ts)) for TIMESTAMP columns.

Snowflake / BigQuery / Databricks DATEDIFF(unit, start, end).

• Signature matches SQL Server — three args: unit first, then start_date, then end_date.
• DATE_DIFF — BigQuery uses DATE_DIFF(end, start, unit) (different arg order from Snowflake).
• Knowing this matters because cloud-warehouse interviews lean heavily on these dialects.
• pyspark datediff — PySpark's datediff(end, start) matches MySQL's two-arg form (days only); for other units use months_between(end, start) or compute manually.

Unit-boundary counting — the universal gotcha.

• DATEDIFF(MONTH, '2026-01-31', '2026-02-01') returns 1 (one month-boundary crossed), even though only 1 day elapsed.
• DATEDIFF(YEAR, '2025-12-31', '2026-01-01') returns 1 for the same reason.
• Correct "full months elapsed" in SQL Server: subtract days first, then divide — or use DATEDIFF(MONTH, start, end) - CASE WHEN DAY(end) < DAY(start) THEN 1 ELSE 0 END.
• PostgreSQL's AGE() returns the calendar-precise interval (1 year 2 months 3 days), avoiding the boundary trap entirely.

SQL interview question — days between signup and first purchase per user

Assume users(user_id, signup_date) and purchases(user_id, purchase_ts). Return per user: the signup date, the date of their first purchase, and the number of days between signup and first purchase. Exclude users who have never purchased.

Solution Using LEFT JOIN + MIN + date subtraction

Code (PostgreSQL).

SELECT u.user_id,
       u.signup_date,
       MIN(p.purchase_ts)::date                       AS first_purchase_date,
       MIN(p.purchase_ts)::date - u.signup_date       AS days_to_first_purchase
FROM users u
JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.user_id, u.signup_date
ORDER BY days_to_first_purchase;

Equivalent in MySQL.

SELECT u.user_id,
       u.signup_date,
       DATE(MIN(p.purchase_ts))                                  AS first_purchase_date,
       DATEDIFF(DATE(MIN(p.purchase_ts)), u.signup_date)         AS days_to_first_purchase
FROM users u
JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.user_id, u.signup_date
ORDER BY days_to_first_purchase;

Equivalent in SQL Server.

SELECT u.user_id,
       u.signup_date,
       CAST(MIN(p.purchase_ts) AS DATE)                                       AS first_purchase_date,
       DATEDIFF(DAY, u.signup_date, CAST(MIN(p.purchase_ts) AS DATE))         AS days_to_first_purchase
FROM users u
JOIN purchases p ON p.user_id = u.user_id
GROUP BY u.user_id, u.signup_date
ORDER BY days_to_first_purchase;

Step-by-step trace.

step	relation	outcome
1	JOIN users with purchases on user_id	only users with at least one purchase survive (INNER JOIN drops never-purchasers)
2	GROUP BY user_id, signup_date	one row per user
3	MIN(p.purchase_ts)	the first purchase per user
4	Cast to DATE and subtract signup_date	day difference per dialect's flavour
5	ORDER BY days_to_first_purchase	quickest-to-purchase users first

Output:

user_id	signup_date	first_purchase_date	days_to_first_purchase
42	2026-04-15	2026-04-15	0
17	2026-04-01	2026-04-08	7
88	2026-03-20	2026-05-10	51

Why this works — concept by concept:

• JOIN over LEFT JOIN — INNER drops users with no purchases; the prompt says to exclude them.
• MIN(p.purchase_ts) gives first purchase — pair with GROUP BY user_id for one row per user.
• Date subtraction returns days — in Postgres, date1 - date2 is an integer day count; in MySQL, use DATEDIFF(end, start); in SQL Server, DATEDIFF(DAY, start, end). The output is identical; only the spelling differs.
• CAST(... AS DATE) drops the time component — ensures the difference is whole days, not fractional days.
• Cost — single hash aggregate over the joined relation; Θ(n) scan + Θ(n) aggregate.

SQL
Topic — date-functions
DATEDIFF SQL drills

Practice →

SQL
Topic — date-arithmetic
Date arithmetic SQL practice

Practice →

---

4. DATE_FORMAT, TO_CHAR, and FORMAT — turning dates into strings

Every dialect has its own sql date format syntax — memorise three patterns and you're done

When a stakeholder asks for "the date formatted as dd/mm/yyyy," you reach for a formatting function. MySQL has DATE_FORMAT. Postgres / Oracle have TO_CHAR. SQL Server has FORMAT (or the legacy CONVERT). SQLite has strftime. The pattern strings differ — and yes, mysql date format / mysql date_format are the two most-searched forms of this same idea.

MySQL DATE_FORMAT(d, pattern) — the most-searched of all.

• Signature — DATE_FORMAT(date_value, '<pattern>') returns a string.
• Pattern tokens — %Y 4-digit year, %y 2-digit, %m zero-padded month, %c un-padded month, %d zero-padded day, %e un-padded day, %H zero-padded hour (24h), %i minute, %s second, %M month name, %W weekday name.
• Common pattern: DATE_FORMAT(d, '%Y-%m-%d') → 2026-05-22 (ISO).
• format date sql in MySQL → DATE_FORMAT(d, '<pattern>').

PostgreSQL / Oracle TO_CHAR(d, pattern).

• Signature — TO_CHAR(date_value, '<pattern>') returns a string.
• Pattern tokens (case-sensitive!) — YYYY 4-digit year, YY 2-digit, MM month number, Mon short month name, Month full month name, DD day, HH24 24-hour, MI minute, SS second, Day weekday name.
• Common pattern: TO_CHAR(d, 'YYYY-MM-DD') → 2026-05-22.
• Common bug — using mm (lowercase) which gets interpreted differently in Oracle.

SQL Server FORMAT(d, pattern) (and the legacy CONVERT).

• FORMAT(d, 'yyyy-MM-dd') — .NET-style format string; case-sensitive in different ways (mm is minute, MM is month).
• Legacy CONVERT(VARCHAR, d, style_code) — style codes: 23 for ISO yyyy-mm-dd, 103 for dd/mm/yyyy, 101 for mm/dd/yyyy, 120 for yyyy-mm-dd hh:mi:ss.
• Cheat — use FORMAT for readability; use CONVERT for performance (function calls in FORMAT are slower at scale).

SQLite strftime(pattern, d).

• C-strftime patterns — '%Y-%m-%d' for ISO, '%d/%m/%Y' for UK style.
• Pattern format matches MySQL's DATE_FORMAT tokens but with different argument order (pattern first).

The three format patterns you'll be asked for, across every dialect.

Output	MySQL (DATE_FORMAT)	Postgres / Oracle (TO_CHAR)	SQL Server (FORMAT)
2026-05-22 (ISO, sql date format yyyy-mm-dd)	'%Y-%m-%d'	'YYYY-MM-DD'	'yyyy-MM-dd'
22/05/2026 (UK, sql date format dd/mm/yyyy)	'%d/%m/%Y'	'DD/MM/YYYY'	'dd/MM/yyyy'
05/22/2026 (US, sql date format mm/dd/yyyy)	'%m/%d/%Y'	'MM/DD/YYYY'	'MM/dd/yyyy'
May 22, 2026 (long)	'%M %d, %Y'	'Month DD, YYYY'	'MMMM dd, yyyy'
Friday (weekday)	'%W'	'Day'	'dddd'

date format in sql — the common interview probe.

• Question — "format 2026-05-22 as 22/05/2026."
• MySQL — DATE_FORMAT(d, '%d/%m/%Y').
• Postgres — TO_CHAR(d, 'DD/MM/YYYY').
• SQL Server — FORMAT(d, 'dd/MM/yyyy').
• date format on sql / date format sql — these are common search misspellings of the same intent.

sql date formats — when to format vs when to keep as DATE.

• Keep as DATE / TIMESTAMP for any column you'll filter, sort, or arithmetic on; conversion to string defeats indexes and breaks comparisons.
• Format to string only for display — final SELECT columns destined for a report or export.
• Never round-trip — CAST(TO_CHAR(d, 'YYYY-MM-DD') AS DATE) is a code smell; just use d::date.

SQL
Topic — date-functions
Date formatting SQL drills

Practice →

SQL
Topic — date-time
Date / time SQL library

Practice →

---

5. EXTRACT and DATE_PART — pulling year, month, day, hour, dow

extract in sql pulls one part of a date; DATE_TRUNC rounds the whole date down

When you need just one component of a date — the year for grouping, the day-of-week for cohort analysis, the hour for traffic peaks — reach for extract in sql. It's the ANSI-standard way to pull a component, and every major dialect supports it.

EXTRACT(field FROM source) — ANSI standard.

• Signature — EXTRACT(<field> FROM <date_or_timestamp>) returns a numeric.
• Fields — YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, DOW (day-of-week), DOY (day-of-year), EPOCH (Unix timestamp), ISOYEAR, ISOWEEK.
• Common use case — EXTRACT(YEAR FROM order_date) = 2026 (but consider date-range comparison for index use).
• Supported by — PostgreSQL, MySQL (since 5.5), SQL Server (as DATEPART), Oracle, Snowflake, BigQuery.

DATE_PART('field', source) — PostgreSQL function-call form.

• Identical semantics to EXTRACT but function-call style.
• Field name is a string — DATE_PART('year', d), DATE_PART('month', d).
• Use either — they compile to the same plan.

MySQL YEAR(), MONTH(), DAY(), HOUR(), WEEK() shortcuts.

• Convenient — YEAR(d) is shorter than EXTRACT(YEAR FROM d).
• Same result — wraps the underlying extraction.
• select year from date sql in MySQL → SELECT YEAR(order_date) FROM orders.
• extracting year from date in sql is the common search variant — answer is EXTRACT(YEAR FROM d) (ANSI) or YEAR(d) (MySQL).

SQL Server DATEPART(unit, date).

• Signature — DATEPART(YEAR, d), DATEPART(MONTH, d), etc.
• Convenience aliases — YEAR(d), MONTH(d), DAY(d).
• extract date from datetime sql — CAST(d AS DATE) (SQL Server) or DATE(d) (MySQL) or d::date (Postgres) drops the time and keeps the calendar day.

Common EXTRACT fields and their typical uses.

• YEAR — annual rollups; GROUP BY EXTRACT(YEAR FROM order_date).
• MONTH — month-of-year (1–12); month-over-month comparisons.
• QUARTER — quarter (1–4); finance reporting.
• WEEK / ISOWEEK — ISO week numbers (1–53); weekly cohort analysis.
• DAY — day-of-month (1–31).
• DOW — day-of-week (0–6 in Postgres, Sunday=0; or 1–7 depending on dialect).
• DOY — day-of-year (1–366); seasonal analysis.
• HOUR — hour-of-day (0–23); traffic-peak analysis.
• EPOCH — Unix timestamp (seconds since 1970-01-01 UTC); useful for time-difference math.

extract month and year from date in sql — the common multi-field probe.

• Postgres — SELECT EXTRACT(YEAR FROM d) AS yr, EXTRACT(MONTH FROM d) AS mo FROM ….
• MySQL — SELECT YEAR(d) AS yr, MONTH(d) AS mo FROM ….
• SQL Server — SELECT YEAR(d) AS yr, MONTH(d) AS mo FROM ….
• For grouping by month — use DATE_TRUNC('month', d) (next section) instead; cleaner output.

The indexed-column trap.

• WHERE EXTRACT(YEAR FROM order_date) = 2026 may NOT use an index on order_date because the planner sees a function call.
• Fix — rewrite as WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'; uses the index.
• Or — create a functional index: CREATE INDEX ON orders ((EXTRACT(YEAR FROM order_date))); Postgres only.

SQL
Topic — date-functions
EXTRACT / DATE_PART drills

Practice →

SQL
Topic — time-series
Time-series SQL practice

Practice →

---

6. DATE_TRUNC — rounding to month, week, day, hour boundaries

date_trunc sql rounds a timestamp down to the start of a period — perfect for time-series GROUP BY

Where EXTRACT pulls a single component, date_trunc sql rounds the entire timestamp down to the start of the specified period. The two functions are siblings, not duplicates: use EXTRACT when you want the number 5 (May); use DATE_TRUNC when you want the date 2026-05-01 00:00:00.

DATE_TRUNC('field', source) — Postgres / Snowflake / Databricks / Redshift.

• Signature — DATE_TRUNC('field_name_lowercase', date_or_timestamp).
• Common fields — 'day', 'week', 'month', 'quarter', 'year', 'hour', 'minute', 'second'.
• Returns — a date or timestamp rounded down to the start of the period (start of the month, midnight of the day, top of the hour).
• date_trunc in sql — the most common phrasing of this same function.

The canonical use case — GROUP BY for time-series rollups.

SELECT DATE_TRUNC('month', sale_date) AS month_start,
       SUM(amount)                    AS monthly_revenue
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month_start;

• One row per calendar month; revenue summed per month.
• Output is a clean month_start date column, perfect for joining to a calendar dimension.
• Far cleaner than GROUP BY EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d) which would produce two columns.

Dialect equivalents.

• MySQL — no DATE_TRUNC; emulate with DATE_FORMAT(d, '%Y-%m-01') for monthly truncation, DATE(d) - INTERVAL WEEKDAY(d) DAY for week-Monday, or DATE(d) for daily.
• SQL Server 2022+ — DATETRUNC('month', d) matches Postgres.
• SQL Server (legacy) — DATEADD(MONTH, DATEDIFF(MONTH, 0, d), 0) for monthly truncation (well-known idiom).
• Oracle — TRUNC(d, 'MM') for monthly, TRUNC(d, 'IW') for ISO week, TRUNC(d) for daily.
• BigQuery — DATE_TRUNC(d, MONTH) (note: unit is unquoted enum, opposite of Postgres).

DATE_TRUNC boundary fields and what they mean.

• 'day' — midnight of the input's date.
• 'week' — the previous Monday (ISO week) — or Sunday in some dialect configurations.
• 'month' — the first of the month.
• 'quarter' — the first day of the quarter (Jan 1, Apr 1, Jul 1, Oct 1).
• 'year' — January 1 of that year.
• 'hour' / 'minute' / 'second' — truncates the smaller components to zero.

Performance.

• GROUP BY DATE_TRUNC(...) on a non-indexed column does a full scan + sort.
• Functional index — CREATE INDEX ON sales ((DATE_TRUNC('month', sale_date))) lets the planner use the index for the group.
• Always combine with a date-range WHERE — WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01' lets the planner trim before truncating.

SQL
Topic — date-functions
DATE_TRUNC SQL drills

Practice →

SQL
Topic — aggregation
Aggregation + DATE_TRUNC patterns

Practice →

---

7. Date arithmetic with INTERVAL, DATE_ADD, and DATEADD

Adding and subtracting durations — every dialect spells it differently

The fifth date-function family is arithmetic — adding or subtracting a duration to produce a new date. Every dialect spells it differently, but the underlying idea is the same: current_date + 7 days should land you a week from now.

PostgreSQL INTERVAL literals — interval sql postgres.

• Add — d + INTERVAL '7 days', d + INTERVAL '1 month', d + INTERVAL '1 year 6 months'.
• Subtract — d - INTERVAL '30 days', now() - INTERVAL '1 hour'.
• Direct number arithmetic for DATE — d + 7 adds 7 days when d is DATE (but NOT for TIMESTAMP).
• Combine units — INTERVAL '1 year 2 months 3 days 04:30:00' is legal.
• sql date 7 days / sql date - 7 — CURRENT_DATE + INTERVAL '7 days' or CURRENT_DATE - 7.

MySQL DATE_ADD / DATE_SUB / inline INTERVAL.

• Function form — DATE_ADD(d, INTERVAL 7 DAY), DATE_SUB(d, INTERVAL 1 MONTH).
• Inline form — d + INTERVAL 7 DAY, d - INTERVAL 1 MONTH (note: bare integers and no quotes around the duration).
• Units — DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, QUARTER.
• Compound — MySQL accepts compound expressions but they're verbose: INTERVAL '1-6' YEAR_MONTH for "1 year 6 months".

SQL Server DATEADD(unit, n, d).

• Signature — DATEADD(<unit>, <count>, <date>). Note: count can be negative for subtraction.
• Units — DAY, WEEK, MONTH, YEAR, QUARTER, HOUR, MINUTE, SECOND.
• Add 7 days — DATEADD(DAY, 7, d).
• Subtract 30 days — DATEADD(DAY, -30, d).

Oracle direct arithmetic + ADD_MONTHS.

• Add days — d + 7 (works directly on DATE columns in Oracle).
• Add months — ADD_MONTHS(d, 1) (handles end-of-month correctly: ADD_MONTHS('2026-01-31', 1) returns 2026-02-28).
• Add years — ADD_MONTHS(d, 12).

Snowflake / BigQuery / Databricks DATEADD(unit, n, d).

• Snowflake — matches SQL Server: DATEADD(DAY, 7, d).
• BigQuery — DATE_ADD(d, INTERVAL 7 DAY) (matches MySQL).
• Databricks — DATE_ADD(d, 7) for days; ADD_MONTHS(d, 1) for months.

Month-end behaviour — the senior-signal trap.

• '2026-01-31' + INTERVAL '1 month' — does it return 2026-02-28 (clamp to month-end) or 2026-03-03 (overflow)?
• Postgres — clamps to 2026-02-28.
• Oracle ADD_MONTHS — clamps to 2026-02-28.
• SQL Server DATEADD(MONTH, 1, d) — also clamps to 2026-02-28.
• State this aloud when an interview question involves month boundaries.

current date sql and sql date now — get "now" and arithmetic from it.

• Postgres — CURRENT_DATE + INTERVAL '30 days', NOW() - INTERVAL '1 hour'.
• MySQL — CURDATE() + INTERVAL 30 DAY, NOW() - INTERVAL 1 HOUR.
• SQL Server — DATEADD(DAY, 30, GETDATE()), DATEADD(HOUR, -1, GETDATE()).
• Oracle — SYSDATE + 30, SYSDATE - 1/24.

SQL interview question — orders in the last 7 days per region

Assume orders(order_id, region, order_ts, amount). Return per region: total orders and total revenue for orders placed in the last 7 days.

Solution Using INTERVAL arithmetic in WHERE

Code (PostgreSQL).

SELECT region,
       COUNT(*)    AS recent_orders,
       SUM(amount) AS recent_revenue
FROM orders
WHERE order_ts >= NOW() - INTERVAL '7 days'
GROUP BY region
ORDER BY recent_revenue DESC;

Equivalent in MySQL.

SELECT region,
       COUNT(*)    AS recent_orders,
       SUM(amount) AS recent_revenue
FROM orders
WHERE order_ts >= NOW() - INTERVAL 7 DAY
GROUP BY region
ORDER BY recent_revenue DESC;

Equivalent in SQL Server.

SELECT region,
       COUNT(*)    AS recent_orders,
       SUM(amount) AS recent_revenue
FROM orders
WHERE order_ts >= DATEADD(DAY, -7, GETDATE())
GROUP BY region
ORDER BY recent_revenue DESC;

Step-by-step trace.

step	relation	outcome
1	NOW() - INTERVAL '7 days' evaluates to a timestamp 7 days ago	one constant
2	WHERE order_ts >= <constant> filters rows	uses an index on order_ts
3	GROUP BY region collapses	one row per region
4	COUNT(*) + SUM(amount)	two metrics per region
5	ORDER BY recent_revenue DESC	sorted output

Output:

region	recent_orders	recent_revenue
US	142	12,500
EU	98	8,750
APAC	64	5,400

Why this works — concept by concept:

• Subtraction in WHERE — moves the constant computation out of the row loop; the planner evaluates NOW() - INTERVAL '7 days' once.
• Half-open interval — order_ts >= cutoff includes today and excludes anything before; safer than BETWEEN for late-night timestamps.
• Indexable predicate — the column order_ts appears bare (no function wrapper); the planner can use a B-tree index on order_ts to skip old rows.
• GROUP BY + two aggregates — single hash aggregate computes both metrics; no extra joins.
• Cost — index seek on order_ts >= cutoff + hash aggregate keyed on region.

SQL
Topic — date-arithmetic
Date arithmetic SQL drills

Practice →

SQL
Topic — date-functions
Date functions library

Practice →

---

8. Common date interview patterns — BETWEEN, rolling windows, age

Eight patterns cover 80% of date-related interview questions

The remaining 20% are dialect-specific edge cases. Memorise these eight and you'll handle most prompts on the first try.

Pattern 1 — date BETWEEN inclusivity (sql date between, sql date range).

• WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31' — INCLUSIVE on both ends for DATE columns.
• WHERE order_ts BETWEEN '2026-01-01' AND '2026-03-31' — for TIMESTAMP columns, the right bound is 2026-03-31 00:00:00, so anything after midnight on March 31 is excluded. Use the half-open form instead: WHERE order_ts >= '2026-01-01' AND order_ts < '2026-04-01'.

Pattern 2 — rolling N-day window.

• WHERE event_ts >= CURRENT_TIMESTAMP - INTERVAL '30 days' AND event_ts < CURRENT_TIMESTAMP.
• MySQL — WHERE event_ts >= NOW() - INTERVAL 30 DAY.
• SQL Server — WHERE event_ts >= DATEADD(DAY, -30, GETDATE()).

Pattern 3 — age in years from a birthdate column.

• Postgres — EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)).
• MySQL — TIMESTAMPDIFF(YEAR, birthdate, CURDATE()).
• SQL Server — DATEDIFF(YEAR, birthdate, GETDATE()) - CASE WHEN MONTH(birthdate) > MONTH(GETDATE()) OR (MONTH(birthdate) = MONTH(GETDATE()) AND DAY(birthdate) > DAY(GETDATE())) THEN 1 ELSE 0 END (must subtract if birthday hasn't happened yet this year).

Pattern 4 — days between two dates.

• Postgres — (end_date - start_date) returns days.
• MySQL — DATEDIFF(end_date, start_date).
• SQL Server — DATEDIFF(DAY, start_date, end_date).

Pattern 5 — first day of current month.

• Postgres — DATE_TRUNC('month', CURRENT_DATE)::date.
• MySQL — DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY).
• SQL Server — DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1).

Pattern 6 — last day of current month.

• Postgres — (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::date.
• MySQL — LAST_DAY(CURDATE()).
• SQL Server — EOMONTH(GETDATE()).

Pattern 7 — day-of-week distribution.

• Postgres — GROUP BY EXTRACT(DOW FROM event_ts) (0=Sunday, 6=Saturday).
• MySQL — GROUP BY DAYOFWEEK(event_ts) (1=Sunday, 7=Saturday) or WEEKDAY(event_ts) (0=Monday, 6=Sunday).
• SQL Server — GROUP BY DATEPART(WEEKDAY, event_ts) (1=Sunday by default, configurable).

Pattern 8 — month-over-month comparison.

• Combine DATE_TRUNC('month', d) with the window function LAG() (see Blog70 §5):

  SELECT DATE_TRUNC('month', sale_date) AS month,
         SUM(amount)                    AS revenue,
         LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', sale_date)) AS prev_month_revenue
  FROM sales
  GROUP BY 1
  ORDER BY 1;

SQL
Topic — time-series
Time-series SQL drills

Practice →

SQL
Topic — date-arithmetic
Date arithmetic patterns

Practice →

---

Choosing the right date function (cheat sheet)

A one-screen cheat sheet for using SQL date functions — pick the family that matches your intent, then the dialect-specific function.

You want to …	Function family	Postgres / Standard	MySQL	SQL Server
Get current date	"Now"	CURRENT_DATE	CURDATE()	GETDATE() (then cast)
Get current timestamp	"Now"	NOW() / CURRENT_TIMESTAMP	NOW()	GETDATE() / SYSDATETIME()
Count days between two dates	Difference	end_date - start_date	DATEDIFF(end, start)	DATEDIFF(DAY, start, end)
Count months / years between	Difference	AGE(end, start) then EXTRACT(YEAR FROM …)	TIMESTAMPDIFF(MONTH, start, end)	DATEDIFF(MONTH, start, end)
Format date as yyyy-mm-dd	Formatting	TO_CHAR(d, 'YYYY-MM-DD')	DATE_FORMAT(d, '%Y-%m-%d')	FORMAT(d, 'yyyy-MM-dd')
Format date as dd/mm/yyyy	Formatting	TO_CHAR(d, 'DD/MM/YYYY')	DATE_FORMAT(d, '%d/%m/%Y')	FORMAT(d, 'dd/MM/yyyy')
Pull year / month / day	Extraction	EXTRACT(YEAR FROM d)	YEAR(d)	YEAR(d)
Truncate to month-start	Truncation	DATE_TRUNC('month', d)	DATE_FORMAT(d, '%Y-%m-01')	DATETRUNC(MONTH, d) (2022+)
Add 7 days	Arithmetic	d + INTERVAL '7 days'	DATE_ADD(d, INTERVAL 7 DAY)	DATEADD(DAY, 7, d)
Subtract 1 month	Arithmetic	d - INTERVAL '1 month'	DATE_SUB(d, INTERVAL 1 MONTH)	DATEADD(MONTH, -1, d)
First day of current month	Truncation	DATE_TRUNC('month', CURRENT_DATE)	DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY)	DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
Last day of current month	Truncation	(DATE_TRUNC('month', d) + INTERVAL '1 month - 1 day')::date	LAST_DAY(d)	EOMONTH(d)
Cast timestamp to date	Conversion	d::date / CAST(d AS DATE)	DATE(d)	CAST(d AS DATE)

---

Frequently asked questions

What's the difference between DATEDIFF in SQL Server, MySQL, and PostgreSQL?

SQL Server's DATEDIFF(unit, start, end) takes three arguments and returns a count of unit boundaries crossed (DAY, MONTH, YEAR, etc.). MySQL's DATEDIFF(end, start) takes only two arguments and returns days only — for other units use TIMESTAMPDIFF(unit, start, end) (note the reversed argument order). PostgreSQL has no built-in DATEDIFF; you subtract dates directly (end - start returns an integer day count for DATE columns or an INTERVAL for TIMESTAMP columns), or use AGE(end, start) for a years-months-days breakdown. Snowflake and BigQuery match SQL Server's signature. When in doubt, write (end - start) for Postgres and DATEDIFF with explicit DAY unit for SQL Server / Snowflake.

How do I format a date as yyyy-MM-dd or dd/MM/yyyy in SQL?

In MySQL, use DATE_FORMAT(d, '%Y-%m-%d') for ISO yyyy-MM-dd or DATE_FORMAT(d, '%d/%m/%Y') for dd/MM/yyyy. In PostgreSQL or Oracle, use TO_CHAR(d, 'YYYY-MM-DD') or TO_CHAR(d, 'DD/MM/YYYY') (note: pattern is case-sensitive). In SQL Server, use FORMAT(d, 'yyyy-MM-dd') or FORMAT(d, 'dd/MM/yyyy') — but FORMAT is slower at scale, so consider CONVERT(VARCHAR, d, 23) for ISO and CONVERT(VARCHAR, d, 103) for dd/mm/yyyy in performance-sensitive queries. SQLite uses strftime('%Y-%m-%d', d). Rule of thumb: keep dates as native DATE/TIMESTAMP for filtering and arithmetic; format to string only at the final output stage.

How do I extract year, month, and day from a date in SQL?

The ANSI-standard form is EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d), EXTRACT(DAY FROM d) — works in PostgreSQL, MySQL 5.5+, Oracle, Snowflake, BigQuery. MySQL also offers shorter aliases: YEAR(d), MONTH(d), DAY(d). SQL Server has the same YEAR(d), MONTH(d), DAY(d) shortcuts plus the general DATEPART(YEAR, d). For combined extract month and year from date in sql, write two separate EXTRACT calls in the SELECT list, or use DATE_TRUNC('month', d) to get a clean month-start date that captures both year and month in one column. To pull the day-of-week, use EXTRACT(DOW FROM d) (Postgres, 0=Sunday) or DAYOFWEEK(d) (MySQL, 1=Sunday).

How do I add or subtract days from a date in SQL?

In PostgreSQL, the cleanest form is d + INTERVAL '7 days' for adding, d - INTERVAL '30 days' for subtracting; if d is a DATE you can also write d + 7 and d - 7 directly (sql date 7 days and sql date - 7). In MySQL, use DATE_ADD(d, INTERVAL 7 DAY) and DATE_SUB(d, INTERVAL 30 DAY), or the inline d + INTERVAL 7 DAY form. In SQL Server, use DATEADD(DAY, 7, d) and DATEADD(DAY, -30, d). For month arithmetic, every dialect handles end-of-month clamping correctly — '2026-01-31' + INTERVAL '1 month' returns 2026-02-28 in Postgres, MySQL, SQL Server, and Oracle (via ADD_MONTHS).

What's the difference between TIMESTAMP and TIMESTAMPTZ in PostgreSQL?

TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE) is a "wall clock" value — it stores year-month-day-hour-minute-second with no associated time zone. TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) is a "moment in time" — Postgres stores it internally as UTC and converts to the session's TimeZone setting on display and on arithmetic. The two types occupy the same 8 bytes on disk; the difference is purely semantic. Use TIMESTAMPTZ for any event that needs to display correctly across regions (orders, logins, sessions); use TIMESTAMP only for wall-clock concepts like business hours or scheduled meeting times. The common bug is mixing types: TIMESTAMP - TIMESTAMPTZ is illegal without an explicit cast or AT TIME ZONE conversion.

---

Practice on PipeCode

PipeCode ships 450+ data-engineering interview problems — including PostgreSQL-first SQL practice keyed to date functions (DATEDIFF, DATE_FORMAT, EXTRACT, DATE_TRUNC), date arithmetic with INTERVAL / DATE_ADD / DATEADD, time-series rollups, and the dialect quirks that fail candidates who memorise only one engine. Whether you're drilling sql interview questions with answers for sql for data engineers loops or grinding through data engineering interview questions end-to-end, the practice library mirrors the same four-family taxonomy this guide teaches.

Kick off via Explore practice →; drill the dedicated date-functions SQL lane →; fan out into the date-arithmetic SQL lane →; rehearse time-series SQL drills →; reinforce aggregation + DATE_TRUNC patterns →; widen coverage on the full SQL practice library →.