SQL date functions cheat sheet

CURRENT_DATE                           -- today, no time
CURRENT_TIMESTAMP                      -- right now, with timezone (ANSI)
NOW()                                  -- Postgres / MySQL / DuckDB
CURRENT_TIMESTAMP()                    -- BigQuery (parens required)
datetime('now')                        -- SQLite
GETDATE()                              -- SQL Server (server-local)
SYSDATETIME()                          -- SQL Server (higher precision)
SYSDATE                                -- Oracle (server-local, no TZ)
LOCALTIME / LOCALTIMESTAMP             -- ANSI: like CURRENT_* without timezone

-- UTC explicitly.
CURRENT_TIMESTAMP AT TIME ZONE 'UTC'   -- Postgres / DuckDB
UTC_TIMESTAMP()                        -- MySQL
GETUTCDATE()                           -- SQL Server

EXTRACT(YEAR    FROM date_col)         -- ANSI; supported almost everywhere
EXTRACT(MONTH   FROM date_col)
EXTRACT(DAY     FROM date_col)
EXTRACT(HOUR    FROM ts_col)
EXTRACT(QUARTER FROM date_col)
EXTRACT(DOW     FROM date_col)         -- day of week (0-6 or 1-7 depending on engine)

DATE_PART('year', date_col)            -- Postgres / DuckDB / Snowflake / Redshift synonym
YEAR(date_col)                         -- MySQL / SQL Server shortcut (Oracle uses EXTRACT)
MONTH(date_col)                        -- same family
DAY(date_col)

DATE_TRUNC('day',   ts_col)            -- Postgres / DuckDB / Snowflake / Redshift / Databricks
DATE_TRUNC('month', ts_col)
DATE_TRUNC('year',  ts_col)
DATE_TRUNC('week',  ts_col)            -- Monday by default on most engines

TIMESTAMP_TRUNC(ts_col, MONTH)         -- BigQuery (TIMESTAMP); DATE_TRUNC for DATE; DATETIME_TRUNC for DATETIME

DATETRUNC(MONTH, ts_col)               -- SQL Server 2022+
TRUNC(date_col, 'MM')                  -- Oracle
date(d, 'start of month')              -- SQLite
DATE_FORMAT(d, '%Y-%m-01')             -- MySQL workaround

-- Add / subtract a period.
date_col + INTERVAL '7 days'           -- Postgres / DuckDB / Oracle
DATE_ADD(date_col, INTERVAL 7 DAY)     -- MySQL / BigQuery
DATEADD(DAY, 7, date_col)              -- SQL Server / Snowflake / Redshift

date_col - INTERVAL '1 month'          -- works on most engines

-- Difference between two dates (in days).
end_date - start_date                  -- Postgres / DuckDB / Oracle
julianday(end) - julianday(start)      -- SQLite
DATEDIFF(end, start)                   -- MySQL: returns days
DATEDIFF(DAY, start, end)              -- SQL Server / Snowflake / Redshift
DATE_DIFF(end, start, DAY)             -- BigQuery (note arg order)
MONTHS_BETWEEN(end, start)             -- Oracle, returns months as fraction

-- "X days/months/years from now."
CURRENT_DATE + INTERVAL '30 days'
DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'   -- last day of last month

-- First day of the month.
DATE_TRUNC('month', date_col)

-- Last day of the month.
DATE_TRUNC('month', date_col) + INTERVAL '1 month' - INTERVAL '1 day'    -- Postgres / DuckDB
LAST_DAY(date_col)                                                        -- MySQL / Oracle / Snowflake / DuckDB
LAST_DAY(date_col, MONTH)                                                 -- BigQuery / Snowflake (with unit)
EOMONTH(date_col)                                                         -- SQL Server (since 2012)

-- First day of the year, quarter, week.
DATE_TRUNC('year',    date_col)
DATE_TRUNC('quarter', date_col)
DATE_TRUNC('week',    date_col)

TO_CHAR(d, 'YYYY-MM-DD')               -- Postgres / Oracle / Snowflake / Redshift
STRFTIME(d, '%Y-%m-%d')                -- DuckDB (canonical)
DATE_FORMAT(d, '%Y-%m-%d')             -- MySQL
FORMAT_DATE('%Y-%m-%d', d)             -- BigQuery
FORMAT(d, 'yyyy-MM-dd')                -- SQL Server
strftime('%Y-%m-%d', d)                -- SQLite

-- ISO format ('2026-04-26'): CAST works everywhere.
CAST('2026-04-26' AS DATE)
'2026-04-26'::DATE                     -- Postgres / DuckDB shorthand

-- Custom format.
TO_DATE('26/04/2026', 'DD/MM/YYYY')    -- Postgres / Oracle / Snowflake / Redshift
STRPTIME('26/04/2026', '%d/%m/%Y')     -- DuckDB (canonical)
STR_TO_DATE('26/04/2026', '%d/%m/%Y')  -- MySQL
PARSE_DATE('%d/%m/%Y', '26/04/2026')   -- BigQuery
CONVERT(DATE, '26/04/2026', 103)       -- SQL Server (103 = "dd/mm/yyyy")

-- Group by month (preserves the year).
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM   orders
GROUP  BY DATE_TRUNC('month', order_date);

-- "Last 30 days" — index-friendly half-open range.
SELECT * FROM orders
WHERE  order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND  order_date <  CURRENT_DATE;

-- Filter to a single year — index-friendly.
SELECT * FROM orders
WHERE  order_date >= DATE '2026-01-01'
  AND  order_date <  DATE '2027-01-01';

-- Year-over-year via self-join on calendar date — robust to gaps and leap years.
-- (LAG(revenue, 365) only works on a perfectly dense daily series.)
WITH daily_revenue AS (
  SELECT order_date AS day, ROUND(SUM(total), 2) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  today.day,
  today.revenue                          AS revenue,
  prev_year.revenue                      AS revenue_year_ago
FROM      daily_revenue today
LEFT JOIN daily_revenue prev_year
  ON prev_year.day = (today.day - INTERVAL '1 year')::DATE;