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.

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. 3 min →
• 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. 2 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →
• 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. 2 min →
• 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. 2 min →
• 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. 4 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →
• 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. 3 min →

Every warehouse spells dates differently.

Each recipe puts the same task next to each other in six dialects, with notes on the gotchas (argument order, boundary counting, DATE vs TIMESTAMP) that quietly produce wrong numbers.

FAQ