PostgreSQL: Convert Text to Date

When you’re importing data from CSV files, APIs, or external systems, dates almost always come in as text strings. You can’t do date arithmetic on strings, can’t filter date ranges properly, and can’t compare dates accurately unless they’re stored as actual DATE types. Converting strings to dates is one of the most common data cleaning tasks you’ll do.

The challenge is that dates arrive in a thousand different formats—some as ISO (2026-02-21), some as US format (02/21/2026), some with month names (21-Feb-2026). PostgreSQL can handle all of them, but you need to know which conversion method to use for each format.

Quick Answer: Cast Text to DATE

To convert text to date:

SELECT
    date_str,
    date_str::DATE as parsed_date,
    (date_str || ' 12:00:00')::TIMESTAMP as as_timestamp
FROM string_dates
WHERE date_str IS NOT NULL
LIMIT 5;

::DATE cast parses ISO format (YYYY-MM-DD) automatically.

How It Works

PostgreSQL recognizes ISO date format by default. Cast with ::DATE and it parses automatically. Other formats require TO_DATE() function.

Different Approaches & When to Use Them

Approach 1: ISO Format (Simplest)

SELECT text_col::DATE as date_col FROM table_name;

For YYYY-MM-DD format.

Approach 2: Custom Format with TO_DATE

SELECT
    TO_DATE(date_str, 'MM/DD/YYYY') as parsed_date,
    TO_DATE(date_str, 'DD-MMM-YYYY') as european_format
FROM strings;

Any custom format.

Approach 3: With Error Handling

SELECT
    CASE
        WHEN (date_str::DATE IS NOT NULL) THEN date_str::DATE
        ELSE NULL
    END as safe_date
FROM strings;

Only valid dates.

Approach 4: Update Column

UPDATE table_name
SET date_col = date_str::DATE
WHERE date_str IS NOT NULL;

Real-World Example: Import CSV Dates

SELECT
    row_num,
    date_str,
    TO_DATE(date_str, 'MM/DD/YYYY') as parsed_date,
    TO_DATE(date_str, 'MM/DD/YYYY') + INTERVAL '1 day' as next_day
FROM csv_import
WHERE date_str IS NOT NULL;

Common Date Formats

-- ISO format
text::DATE  -- 2026-02-21

-- US format
TO_DATE(text, 'MM/DD/YYYY')  -- 02/21/2026

-- European format
TO_DATE(text, 'DD.MM.YYYY')  -- 21.02.2026

-- With month name
TO_DATE(text, 'DD-MMM-YYYY')  -- 21-Feb-2026

-- Full timestamp
TO_TIMESTAMP(text, 'YYYY-MM-DD HH24:MI:SS')

FAQ

Q: What format does PostgreSQL expect by default? A: ISO 8601: YYYY-MM-DD. Use TO_DATE for other formats.

Q: What if the date string is invalid? A: PostgreSQL throws an error. Use CASE or WHERE to filter.

Q: Can I convert dates with times? A: Use ::TIMESTAMP instead: text::TIMESTAMP.

Q: Performance for bulk conversion? A: Very fast. Safe to do on all rows.

Q: What about NULL handling? A: NULL::DATE returns NULL. Check with IS NOT NULL.

Wrapping Up

Convert text to date with ::DATE for ISO format, or TO_DATE(text, 'format') for custom formats. Always validate before conversion in production.