Recipe

Convert string to date (and date to string)

Short answer: if the string is in ISO format ('2026-04-26'), CAST(s AS DATE) works on every engine. For non-ISO formats, use TO_DATE (Postgres / Oracle / Snowflake / Redshift), STR_TO_DATE (MySQL), PARSE_DATE (BigQuery), STRPTIME (DuckDB), CONVERT or TRY_PARSE (SQL Server). SQLite has no format-string parser; it only accepts ISO-ish input via date(). To go the other direction: TO_CHAR (Postgres / Oracle / Snowflake / Redshift), DATE_FORMAT (MySQL), FORMAT_DATE (BigQuery), STRFTIME (DuckDB / SQLite), FORMAT (SQL Server).

Canonical SQL #

sql

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

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

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

Example #

Loading SQL editor...

Format-string cheat sheet #

Two competing format-string conventions: the Postgres / Oracle family uses YYYY-MM-DD HH24:MI:SS; the strftime family uses %Y-%m-%d %H:%M:%S. DuckDB and BigQuery use the strftime convention; MySQL's format strings are strftime-ish but diverge on minutes.

What	Postgres / Oracle / Snowflake / Redshift	strftime family (DuckDB / BigQuery)	MySQL
4-digit year	`YYYY`	`%Y`	`%Y`
2-digit month	`MM`	`%m`	`%m`
2-digit day	`DD`	`%d`	`%d`
Abbreviated month	`Mon`	`%b`	`%b`
Full month name	`Month`	`%B`	`%M`
24-hour	`HH24`	`%H`	`%H`
Minute	`MI`	`%M`	`%i`
Second	`SS`	`%S`	`%s`

(MySQL repurposes %M for the month name and %i for minutes, the classic trap when porting format strings. SQLite's strftime follows the strftime convention for formatting, but SQLite has no format-string parsing function.)

Common variants #

sql

-- Safe parse: NULL on invalid input.
SELECT TRY_CAST('not-a-date' AS DATE)       AS d;    -- DuckDB / SQL Server
SELECT SAFE_CAST('not-a-date' AS DATE)      AS d;    -- BigQuery
SELECT TO_DATE('not-a-date', 'YYYY-MM-DD')          -- Postgres: ERRORS
       -- ...wrap a query in BEGIN/EXCEPTION on Postgres, or pre-filter with a regex.

-- Parsing timestamps (with time component).
SELECT CAST('2026-04-26 14:30:00' AS TIMESTAMP);                       -- ANSI
SELECT TO_TIMESTAMP('26/04/2026 14:30', 'DD/MM/YYYY HH24:MI');         -- Postgres
SELECT STR_TO_DATE('26/04/2026 14:30', '%d/%m/%Y %H:%i');              -- MySQL

-- Date to text in a custom format.
SELECT TO_CHAR(order_date, 'Mon DD, YYYY')   FROM orders;              -- "Apr 26, 2026"
SELECT DATE_FORMAT(order_date, '%b %d, %Y') FROM orders;               -- MySQL

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`TO_DATE(str, 'DD/MM/YYYY')`	errors on bad input; no native `TRY_` variant
MySQL	`STR_TO_DATE(str, '%d/%m/%Y')`	`%i` is minutes, `%M` is month name
SQL Server	`CONVERT(DATE, str, 103)`	numeric style codes, not format strings; `TRY_CONVERT` for NULL-on-fail
BigQuery	`PARSE_DATE('%d/%m/%Y', str)`	format string comes first
Snowflake	`TO_DATE(str, 'DD/MM/YYYY')`	`TRY_TO_DATE` returns NULL on bad input
Oracle	`TO_DATE(str, 'DD/MM/YYYY')`	`MM` is month, `MI` is minutes
SQLite	`date(str)`	ISO-ish input only — no format-string parser
DuckDB	`STRPTIME(str, '%d/%m/%Y')`	no `TO_DATE`; strftime-style format strings

Dialect notes #

Postgres / Oracle / Snowflake / Redshift: TO_DATE, TO_TIMESTAMP, TO_CHAR — same family. Postgres-style format strings. (DuckDB is not in this family: it has no TO_DATE/TO_CHAR, and its TO_TIMESTAMP takes epoch seconds, not a format string.)
MySQL: STR_TO_DATE (parse), DATE_FORMAT (format). strftime-ish format strings.
BigQuery: PARSE_DATE, PARSE_DATETIME, PARSE_TIMESTAMP to parse; FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIMESTAMP to format. strftime format strings.
SQL Server: CONVERT with a numeric style code (101 = US, 103 = British, 120 = ODBC), PARSE / TRY_PARSE with a culture, or FORMAT with a .NET format string. Each has performance and locale tradeoffs.
SQLite: strftime('%Y-%m-%d', date_col) and date(date_col) for parsing/formatting; only ISO-ish input is reliable.
DuckDB: STRPTIME for parsing with a format string, STRFTIME for formatting — strftime convention.

Warning

Always pin the format string for non-ISO input. CAST('04/05/2026' AS DATE) may parse as April 5 or May 4 depending on engine, locale, or session settings. The same string can mean different dates in the same database under different sessions. The safe pattern: explicit TO_DATE('04/05/2026', 'DD/MM/YYYY'). Even better, fix it at the source so you never have to parse the same date string twice.