SQL Date Functions Cheat Sheet: DATE_TRUNC, DATEDIFF, and Every Function You Need

Date and time manipulation is one of the most tested topics in data analyst interviews — and one of the most frustrating, because every database handles it slightly differently.

This cheat sheet covers the core functions you need, shows the differences between databases, and maps each function to the interview questions where it actually appears.

1. Getting the current date and time

sql
-- Current timestamp (date + time)
SELECT NOW();                    -- PostgreSQL, MySQL
SELECT CURRENT_TIMESTAMP;       -- Standard SQL, works everywhere
SELECT GETDATE();               -- SQL Server

-- Current date only (no time)
SELECT CURRENT_DATE;            -- PostgreSQL, MySQL, BigQuery
SELECT CAST(NOW() AS DATE);     -- Works everywhere

2. DATE_TRUNC — the most important date function

DATE_TRUNC rounds a timestamp down to the start of a time unit (month, week, year, etc.).

sql
-- Round to start of month
SELECT DATE_TRUNC('month', order_date) AS month_start
FROM orders;
-- 2024-03-15 → 2024-03-01

-- Group revenue by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Common truncation units:

Database differences:

• PostgreSQL / BigQuery / Snowflake: DATE_TRUNC('month', date_col)
• MySQL: DATE_FORMAT(date_col, '%Y-%m-01') or use DATE_TRUNC in MySQL 8.0+
• SQL Server: DATETRUNC(month, date_col) (SQL Server 2022+) or DATEFROMPARTS(YEAR(date_col), MONTH(date_col), 1)

3. EXTRACT — pulling out date parts

EXTRACT returns a specific part of a date as a number.

sql
SELECT
  order_id,
  EXTRACT(YEAR  FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DAY   FROM order_date) AS day,
  EXTRACT(DOW   FROM order_date) AS day_of_week  -- 0=Sunday, 6=Saturday
FROM orders;

Common uses in interviews:

sql
-- Filter for Q4 only
WHERE EXTRACT(MONTH FROM order_date) IN (10, 11, 12)

-- Filter for weekdays only
WHERE EXTRACT(DOW FROM order_date) NOT IN (0, 6)

-- Count orders per day of week
SELECT
  EXTRACT(DOW FROM order_date) AS day_num,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

4. Date arithmetic — adding and subtracting dates

sql
-- Add days, months, years
SELECT order_date + INTERVAL '7 days'  AS one_week_later  -- PostgreSQL
SELECT DATE_ADD(order_date, INTERVAL 7 DAY)               -- MySQL

-- Subtract to get "last 30 days"
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
WHERE order_date >= NOW() - INTERVAL '30 days'

-- Last complete month
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE)

5. DATEDIFF — calculating the difference between two dates

sql
-- PostgreSQL: subtract dates directly
SELECT order_date - signup_date AS days_to_first_order
FROM customers
JOIN orders USING (customer_id);

-- Or use AGE
SELECT AGE(delivery_date, order_date) AS delivery_time
FROM orders;

-- MySQL / SQL Server
SELECT DATEDIFF(delivery_date, order_date) AS days_to_deliver
FROM orders;

-- BigQuery
SELECT DATE_DIFF(delivery_date, order_date, DAY) AS days_to_deliver
FROM orders;

Interview pattern — time between events:

sql
-- Average days between signup and first order
SELECT
  AVG(first_order.order_date - c.signup_date) AS avg_days_to_convert
FROM customers c
JOIN (
  SELECT customer_id, MIN(order_date) AS order_date
  FROM orders
  GROUP BY customer_id
) first_order USING (customer_id);

6. TO_CHAR / FORMAT — formatting dates for display

sql
-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month_label   -- "2024-03"
SELECT TO_CHAR(order_date, 'Mon YYYY') AS month_label  -- "Mar 2024"
SELECT TO_CHAR(order_date, 'Day') AS day_name          -- "Friday   "

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_label
SELECT DAYNAME(order_date) AS day_name

-- BigQuery
SELECT FORMAT_DATE('%Y-%m', order_date) AS month_label

7. Common interview time-series patterns

Pattern 1: Monthly active users

sql
SELECT
  DATE_TRUNC('month', activity_date) AS month,
  COUNT(DISTINCT user_id) AS mau
FROM user_events
GROUP BY 1
ORDER BY 1;

Pattern 2: Week-over-week growth

sql
WITH weekly AS (
  SELECT
    DATE_TRUNC('week', order_date) AS week,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  week,
  revenue,
  LAG(revenue) OVER (ORDER BY week) AS prev_week_revenue,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
    / LAG(revenue) OVER (ORDER BY week), 2) AS wow_pct
FROM weekly
ORDER BY week;

Pattern 3: Filter for last N complete days

sql
-- Last 7 complete days (excludes today, which may be partial)
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
  AND order_date <  CURRENT_DATE

Pattern 4: Cohort analysis by signup month

sql
SELECT
  DATE_TRUNC('month', signup_date) AS cohort_month,
  COUNT(*) AS cohort_size,
  COUNT(CASE WHEN last_active >= signup_date + INTERVAL '30 days' THEN 1 END) AS retained_30d
FROM customers
GROUP BY 1
ORDER BY 1;

8. Quick reference: database differences

Interview tip: TheQueryLab uses PostgreSQL. In interviews, state your assumption — "I will use PostgreSQL syntax, let me know if you need a different dialect."

9. Practice date function problems on TheQueryLab

Date functions appear in almost every intermediate-to-advanced SQL interview question. The best practice problems involve:

• Calculating time between events (lead time, churn, conversion)
• Grouping by time periods (daily/weekly/monthly trends)
• Filtering for specific date windows
• Computing rolling metrics (7-day average, 30-day active users)

Browse the SQL Interview Preparation Kit on TheQueryLab — the time-series and analytics problems will build your date function intuition faster than reading documentation alone.