MySQL · Postgres · BigQuery · Snowflake · SQLite · DuckDB
The same date query, every SQL dialect.
Date arithmetic, DATE_TRUNC, formatting, "days between", generate-series, time
zones — each shown side by side across six databases. Switch warehouses without
re-learning the date functions every time.
15 recipes
- Basics Convert a String to a Date in SQL (per Dialect) Parse '2024-01-15' (and custom formats) into a DATE in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Basics Get the Current Date & Time in SQL (per Dialect) CURRENT_DATE, NOW() and current timestamp in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Basics Format a Date as a String in SQL (per Dialect) Turn a DATE/timestamp into a formatted text label (YYYY-MM-DD, 'Jan 2024', etc.) in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Arithmetic Add or Subtract Days, Months & Years in SQL (per Dialect) Date arithmetic — add 7 days, subtract a month, add a year — in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Arithmetic Calculate Age in Years from a Birthdate in SQL (per Dialect) Get full completed years (true age) from a birthdate in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB — handling the birthday-not-yet-reached case. Copy-paste.
- Arithmetic Days Between Two Dates in SQL (DATEDIFF per Dialect) Count days (or months) between two dates in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB — with the argument-order traps. Copy-paste.
- Parts & truncation Truncate a Timestamp to Day/Week/Month in SQL (DATE_TRUNC per Dialect) Round a timestamp down to the start of the day, week, or month in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Parts & truncation Day of Week & Week Number in SQL (per Dialect) Get the day-of-week number, the weekday name, and the ISO week number in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB — with the numbering traps. Copy-paste.
- Parts & truncation Extract Year, Month or Day from a Date in SQL (EXTRACT per Dialect) Pull the year, month, day, hour or quarter out of a date/timestamp in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Ranges First & Last Day of the Month in SQL (per Dialect) Get the first and last day of a date's month in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Ranges Generate a Series of Dates in SQL (Calendar Table per Dialect) Produce one row per day between two dates — generate_series, GENERATE_DATE_ARRAY, and recursive CTEs — in PostgreSQL, DuckDB, BigQuery, Snowflake, MySQL and SQLite. Copy-paste.
- Ranges Group / Aggregate by Month (or Week) in SQL (per Dialect) Roll rows up to a monthly (or weekly) total in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Timestamps & TZ Filter Rows in a Date Range in SQL (Last 7/30 Days, This Month) WHERE clauses for 'last 7 days', 'last 30 days', 'today', and 'this month' in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Timestamps & TZ Convert a Timestamp Between Time Zones in SQL (per Dialect) Convert a UTC timestamp to a local zone (e.g. America/New_York) in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
- Timestamps & TZ Unix / Epoch Timestamp ↔ Date in SQL (per Dialect) Convert a Unix epoch (seconds) to a timestamp and back in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB. Copy-paste.
Why this exists
Every warehouse spells dates differently.
Move from Postgres to BigQuery, or Snowflake to DuckDB, and the date functions you know stop working — wrong name, wrong argument order, or a builtin that simply isn't there. datesql.pages.dev puts the same task next to each other in six dialects, so you copy the one for your engine and move on — with notes on the gotchas (argument order, boundary counting, DATE vs TIMESTAMP) that quietly produce wrong numbers.
How it works
Find the task, copy your dialect, run it
- Pick a recipe. Browse by task in the full recipe list.
- Copy your dialect's block. Each recipe has MySQL, Postgres, BigQuery, Snowflake, SQLite and DuckDB versions.
- Mind the note. Each recipe flags the per-dialect gotcha so you don't ship an off-by-one date.
FAQ
Frequently asked questions
Are these SQL recipes free?
Yes. Every recipe on datesql.pages.dev is free to read and copy, with no account, paywall, or sign-up. Some outbound links (for example to data tools or warehouses) may be affiliate links, which never change the price you pay.
Which SQL dialects are covered?
Each recipe shows the same task in MySQL, PostgreSQL, BigQuery, Snowflake, SQLite and DuckDB — the six engines analysts and engineers switch between most. Where a dialect lacks a clean builtin, the recipe gives the correct workaround.
Why do date functions differ so much between databases?
There is no single SQL standard everyone follows for dates — MySQL has DATE_ADD, Snowflake has DATEADD, BigQuery has DATE_ADD with a different argument order, Postgres uses INTERVAL math, SQLite uses string modifiers. That mismatch is exactly what these side-by-side recipes solve.
Why does DATEDIFF give different answers in different dialects?
Two reasons: argument order (MySQL is DATEDIFF(end, start); Snowflake/BigQuery put the unit first and start before end), and boundary counting (some count crossed boundaries, not full units). Each recipe notes the order and whether it counts whole units — see the date-difference and age recipes.
Date vs DATETIME vs TIMESTAMP — does it matter?
Yes. A DATE has no time; TIMESTAMP/DATETIME do, and time zones only apply to timestamp types. Recipes note when a function needs a date vs a timestamp, and the time-zone recipe covers converting between zones.
How do I truncate a timestamp to the month?
Most engines have DATE_TRUNC (Postgres/Snowflake/DuckDB: DATE_TRUNC('month', d); BigQuery: DATE_TRUNC(d, MONTH)). MySQL has no DATE_TRUNC — use DATE_FORMAT(d, '%Y-%m-01'); SQLite uses date(d, 'start of month'). The date_trunc recipe shows all of them.