sql

🔷 SQL: Date Manipulation Cheat Sheet

DATE_TRUNC('month', date)         -- 2024-03-15 → 2024-03-01
EXTRACT(MONTH FROM date)          -- 2024-03-15 → 3
EXTRACT(DOW FROM date)            -- 0=Sunday, 6=Saturday (PostgreSQL)
date + INTERVAL '1 day'           -- Add time
DATEDIFF('day', start, end)       -- Days between (Snowflake/Redshift)
AGE(end_date, start_date)         -- PostgreSQL interval
DATE_PART('year', date)           -- Extract year as number

Practice Questions

Q: Write a query to get the first day of each user's signup month.

A: SELECT user_id, DATE_TRUNC('month', signup_date) AS signup_month FROM users