Eric Campbell 5 min read Engineering

SQL - Convert String to Date

Every database developer eventually faces the same problem: dates stored as strings. Whether it's data imported from CSV files, user input from web forms, legacy systems that predate proper date...

Key Insights

  • Use database-specific functions like STR_TO_DATE() (MySQL), TO_DATE() (PostgreSQL/Oracle), or CONVERT() with style codes (SQL Server) for reliable string-to-date conversion—ANSI SQL CAST() only works with ISO 8601 formats.
  • Always use “TRY” variants (TRY_CAST(), TRY_CONVERT()) when dealing with user input or imported data to gracefully handle invalid dates without crashing your queries.
  • Never apply date conversion functions to columns in WHERE clauses—convert your comparison values instead to preserve index usage and avoid full table scans.

Why String-to-Date Conversion Matters

Every database developer eventually faces the same problem: dates stored as strings. Whether it’s data imported from CSV files, user input from web forms, legacy systems that predate proper date types, or third-party APIs returning JSON with string timestamps—you’ll need to convert these strings into proper date types.

Getting this right matters. Proper date types enable date arithmetic, correct sorting, efficient indexing, and accurate comparisons. A string “2024-01-15” sorts differently than a date 2024-01-15 when mixed with “2024-02-01”. Queries that should take milliseconds can take minutes when date conversions happen on every row.

Let’s cover the practical approaches across major database systems.

Standard SQL CAST and CONVERT Functions

ANSI SQL provides CAST() as the standard way to convert between data types. It works across most databases but has a significant limitation: it only reliably handles ISO 8601 format dates (YYYY-MM-DD).

-- ANSI SQL standard approach
SELECT CAST('2024-01-15' AS DATE);

-- Also works for timestamps
SELECT CAST('2024-01-15 14:30:00' AS TIMESTAMP);

SQL Server also supports a CONVERT() function that predates the ANSI standard:

-- SQL Server CONVERT syntax
SELECT CONVERT(DATE, '2024-01-15');

-- With style code for specific formats
SELECT CONVERT(DATE, '01/15/2024', 101);  -- US format MM/DD/YYYY

The problem with CAST() is that it fails hard on non-standard formats:

-- These will fail with CAST
SELECT CAST('15/01/2024' AS DATE);      -- DD/MM/YYYY format
SELECT CAST('January 15, 2024' AS DATE); -- Written format
SELECT CAST('20240115' AS DATE);         -- Compact format

For anything beyond ISO format, you need database-specific functions.

Database-Specific Functions

Each major database has its own function for parsing date strings with custom formats. Here’s the same date converted across all major platforms:

MySQL: STR_TO_DATE()

-- MySQL uses STR_TO_DATE with format specifiers
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');
SELECT STR_TO_DATE('January 15, 2024', '%M %d, %Y');
SELECT STR_TO_DATE('20240115', '%Y%m%d');

-- For timestamps
SELECT STR_TO_DATE('15-01-2024 14:30:00', '%d-%m-%Y %H:%i:%s');

PostgreSQL: TO_DATE() and TO_TIMESTAMP()

-- PostgreSQL uses TO_DATE with format patterns
SELECT TO_DATE('15/01/2024', 'DD/MM/YYYY');
SELECT TO_DATE('January 15, 2024', 'Month DD, YYYY');
SELECT TO_DATE('20240115', 'YYYYMMDD');

-- For timestamps use TO_TIMESTAMP
SELECT TO_TIMESTAMP('15-01-2024 14:30:00', 'DD-MM-YYYY HH24:MI:SS');

SQL Server: CONVERT() with Style Codes

-- SQL Server uses style codes instead of format strings
SELECT CONVERT(DATE, '01/15/2024', 101);  -- US: MM/DD/YYYY
SELECT CONVERT(DATE, '15/01/2024', 103);  -- UK: DD/MM/YYYY
SELECT CONVERT(DATE, '20240115', 112);    -- ISO compact: YYYYMMDD
SELECT CONVERT(DATE, '2024-01-15', 23);   -- ISO: YYYY-MM-DD

-- For datetime with time
SELECT CONVERT(DATETIME, '2024-01-15 14:30:00', 120);

Oracle: TO_DATE()

-- Oracle uses TO_DATE with format models
SELECT TO_DATE('15/01/2024', 'DD/MM/YYYY') FROM DUAL;
SELECT TO_DATE('January 15, 2024', 'Month DD, YYYY') FROM DUAL;
SELECT TO_DATE('20240115', 'YYYYMMDD') FROM DUAL;

-- For timestamps
SELECT TO_TIMESTAMP('15-01-2024 14:30:00', 'DD-MM-YYYY HH24:MI:SS') FROM DUAL;

Handling Date Format Patterns

The trickiest part of string-to-date conversion is matching your format string to your actual data. Here are the common format specifiers across databases:

Component MySQL PostgreSQL Oracle SQL Server (Style)
4-digit year %Y YYYY YYYY -
2-digit year %y YY YY -
Month (01-12) %m MM MM -
Month name %M Month Month -
Month abbrev %b Mon Mon -
Day (01-31) %d DD DD -
Hour (00-23) %H HH24 HH24 -
Hour (01-12) %h HH12 HH12 -
Minute %i MI MI -
Second %s SS SS -

Common Format Examples

Here’s how to handle the most frequent date formats you’ll encounter:

-- MM/DD/YYYY (US format)
-- MySQL
SELECT STR_TO_DATE('01/15/2024', '%m/%d/%Y');
-- PostgreSQL
SELECT TO_DATE('01/15/2024', 'MM/DD/YYYY');
-- SQL Server
SELECT CONVERT(DATE, '01/15/2024', 101);

-- DD/MM/YYYY (European format)
-- MySQL
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');
-- PostgreSQL
SELECT TO_DATE('15/01/2024', 'DD/MM/YYYY');
-- SQL Server
SELECT CONVERT(DATE, '15/01/2024', 103);

-- YYYYMMDD (Compact/sortable format)
-- MySQL
SELECT STR_TO_DATE('20240115', '%Y%m%d');
-- PostgreSQL
SELECT TO_DATE('20240115', 'YYYYMMDD');
-- SQL Server
SELECT CONVERT(DATE, '20240115', 112);

Watch out for the MM/DD vs DD/MM ambiguity. The string ‘01/02/2024’ could be January 2nd or February 1st depending on locale. When importing data, always verify the source format before writing your conversion.

Error Handling and Validation

Real-world data is messy. You’ll encounter empty strings, typos, impossible dates like ‘2024-02-30’, and completely invalid values. Standard conversion functions throw errors on bad data, which can crash batch processes.

SQL Server: TRY_CONVERT() and TRY_CAST()

-- Returns NULL instead of throwing an error
SELECT TRY_CONVERT(DATE, 'not a date', 101);  -- Returns NULL
SELECT TRY_CONVERT(DATE, '02/30/2024', 101);  -- Returns NULL (invalid date)
SELECT TRY_CONVERT(DATE, '01/15/2024', 101);  -- Returns 2024-01-15

-- Use in data cleaning
SELECT 
    raw_date_string,
    TRY_CONVERT(DATE, raw_date_string, 101) AS parsed_date,
    CASE 
        WHEN TRY_CONVERT(DATE, raw_date_string, 101) IS NULL 
        THEN 'Invalid date format'
        ELSE 'OK'
    END AS validation_status
FROM imported_data;

PostgreSQL: Exception Handling in Functions

PostgreSQL doesn’t have a built-in TRY_CONVERT, but you can create one:

CREATE OR REPLACE FUNCTION safe_to_date(text, text)
RETURNS DATE AS $$
BEGIN
    RETURN TO_DATE($1, $2);
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_to_date('not a date', 'YYYY-MM-DD');  -- Returns NULL
SELECT safe_to_date('2024-01-15', 'YYYY-MM-DD');  -- Returns date

MySQL: Validation Before Conversion

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

-- Validate with a CASE expression
SELECT 
    date_string,
    CASE 
        WHEN date_string REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
        THEN STR_TO_DATE(date_string, '%Y-%m-%d')
        ELSE NULL
    END AS parsed_date
FROM imported_data;

Performance Considerations

Date conversions in the wrong place can destroy query performance. The cardinal rule: never apply functions to indexed columns in WHERE clauses.

The Wrong Way

-- BAD: Converts every row's date before comparison
-- Cannot use index on order_date column
SELECT * FROM orders
WHERE CAST(order_date_string AS DATE) = '2024-01-15';

-- BAD: Function on column prevents index usage
SELECT * FROM orders
WHERE TO_DATE(order_date_string, 'YYYY-MM-DD') BETWEEN '2024-01-01' AND '2024-01-31';

The Right Way

-- GOOD: Convert the comparison value, not the column
-- If order_date is already a DATE type
SELECT * FROM orders
WHERE order_date = CAST('2024-01-15' AS DATE);

-- GOOD: Use a computed/generated column for string dates
ALTER TABLE orders 
ADD order_date_parsed AS (TRY_CONVERT(DATE, order_date_string, 101)) PERSISTED;

CREATE INDEX idx_order_date ON orders(order_date_parsed);

-- Now this query uses the index
SELECT * FROM orders
WHERE order_date_parsed = '2024-01-15';

For batch processing large datasets, convert dates once during import rather than repeatedly in queries:

-- One-time conversion during data load
INSERT INTO orders (order_id, order_date, customer_id)
SELECT 
    order_id,
    TRY_CONVERT(DATE, order_date_string, 101),
    customer_id
FROM staging_orders
WHERE TRY_CONVERT(DATE, order_date_string, 101) IS NOT NULL;

Quick Reference Summary

Database Function Example
MySQL STR_TO_DATE(string, format) STR_TO_DATE('15/01/2024', '%d/%m/%Y')
PostgreSQL TO_DATE(string, format) TO_DATE('15/01/2024', 'DD/MM/YYYY')
SQL Server CONVERT(DATE, string, style) CONVERT(DATE, '15/01/2024', 103)
SQL Server TRY_CONVERT(DATE, string, style) TRY_CONVERT(DATE, '15/01/2024', 103)
Oracle TO_DATE(string, format) TO_DATE('15/01/2024', 'DD/MM/YYYY')
ANSI SQL CAST(string AS DATE) CAST('2024-01-15' AS DATE)

Common SQL Server Style Codes:

  • 101: MM/DD/YYYY (US)
  • 103: DD/MM/YYYY (UK/European)
  • 112: YYYYMMDD (ISO compact)
  • 120: YYYY-MM-DD HH:MI:SS (ODBC canonical)
  • 23: YYYY-MM-DD (ISO8601)

Store dates as proper date types from the start whenever possible. When you inherit string dates, convert them once at the boundary of your system—during import or in a staging table—then work with proper types everywhere else.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.