How to Convert String to Date in SQL (All Databases)

Introduction

Date conversion is one of the most database-specific operations in SQL. Every database uses different functions and format codes. This guide covers the exact syntax for each.

MySQL: STR_TO_DATE

MySQL uses STR_TO_DATE with format specifiers like %Y (4-digit year), %m (month), %d (day).

-- Common formats:
SELECT STR_TO_DATE('2026-03-12', '%Y-%m-%d');         -- ISO format
SELECT STR_TO_DATE('03/12/2026', '%m/%d/%Y');         -- US format
SELECT STR_TO_DATE('12-Mar-2026', '%d-%b-%Y');        -- Day-Mon-Year
SELECT STR_TO_DATE('March 12, 2026', '%M %d, %Y');   -- Full month name

-- With time:
SELECT STR_TO_DATE('2026-03-12 14:30:00', '%Y-%m-%d %H:%i:%s');

-- Format codes:
-- %Y = 4-digit year    %y = 2-digit year
-- %m = month (01-12)   %d = day (01-31)
-- %H = hour (00-23)    %i = minute    %s = second
-- %b = abbreviated month (Jan, Feb)
-- %M = full month name (January, February)

PostgreSQL: TO_DATE and CAST

PostgreSQL uses TO_DATE with format patterns, or simple CAST for ISO format strings.

-- CAST (simplest for ISO format):
SELECT CAST('2026-03-12' AS DATE);
SELECT '2026-03-12'::DATE;  -- PostgreSQL shorthand

-- TO_DATE for custom formats:
SELECT TO_DATE('03/12/2026', 'MM/DD/YYYY');
SELECT TO_DATE('12-Mar-2026', 'DD-Mon-YYYY');
SELECT TO_DATE('March 12, 2026', 'Month DD, YYYY');

-- TO_TIMESTAMP for datetime strings:
SELECT TO_TIMESTAMP('2026-03-12 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- Format patterns:
-- YYYY = 4-digit year   MM = month   DD = day
-- HH24 = 24-hour      MI = minute   SS = second
-- Mon = abbreviated month   Month = full month

SQL Server: CONVERT and TRY_CONVERT

SQL Server uses CONVERT with style codes, or TRY_CONVERT for safe conversion that returns NULL on failure.

-- CAST (ISO format):
SELECT CAST('2026-03-12' AS DATE);

-- CONVERT with style codes:
SELECT CONVERT(DATE, '03/12/2026', 101);  -- US: mm/dd/yyyy
SELECT CONVERT(DATE, '12/03/2026', 103);  -- UK: dd/mm/yyyy
SELECT CONVERT(DATE, '2026-03-12', 23);   -- ISO: yyyy-mm-dd
SELECT CONVERT(DATE, '12 Mar 2026', 106); -- dd mon yyyy

-- TRY_CONVERT (safe, returns NULL on failure):
SELECT TRY_CONVERT(DATE, 'not-a-date', 23);  -- Returns NULL
SELECT TRY_CONVERT(DATE, '2026-03-12', 23);  -- Returns 2026-03-12

-- Common style codes:
-- 101 = mm/dd/yyyy (US)    103 = dd/mm/yyyy (UK)
-- 23  = yyyy-mm-dd (ISO)   120 = yyyy-mm-dd hh:mi:ss

Oracle: TO_DATE

Oracle uses TO_DATE with format masks. It's similar to PostgreSQL but with some different patterns.

-- TO_DATE with format mask:
SELECT TO_DATE('2026-03-12', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('03/12/2026', 'MM/DD/YYYY') FROM DUAL;
SELECT TO_DATE('12-MAR-2026', 'DD-MON-YYYY') FROM DUAL;

-- With time:
SELECT TO_DATE('2026-03-12 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

-- Oracle format elements:
-- YYYY = year     MM = month     DD = day
-- HH24 = 24h     HH = 12h      MI = minute   SS = second
-- MON = abbreviated month     MONTH = full month

Handle Invalid Dates Safely

Real-world data often contains invalid dates. Use safe conversion functions to avoid errors.

-- SQL Server: TRY_CONVERT
SELECT TRY_CONVERT(DATE, date_string, 23)
FROM raw_data
WHERE TRY_CONVERT(DATE, date_string, 23) IS NOT NULL;

-- PostgreSQL: use a CASE or exception handling
SELECT
  CASE
    WHEN date_string ~ '^\d{4}-\d{2}-\d{2}$'
    THEN date_string::DATE
    ELSE NULL
  END AS parsed_date
FROM raw_data;

-- MySQL: STR_TO_DATE returns NULL for invalid dates
SELECT STR_TO_DATE('not-a-date', '%Y-%m-%d');  -- Returns NULL

Best Practices

• Store dates as DATE/TIMESTAMP types, not strings
• Use ISO 8601 format (YYYY-MM-DD) for date strings — it's unambiguous across all databases
• Use safe conversion functions (TRY_CONVERT, STR_TO_DATE) for user input
• Be aware of timezone handling when converting timestamp strings
• Test with edge cases: leap years, month boundaries, different centuries

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions