PostgreSQL: Check If String Is Date

When you’re importing messy data, you need to know which rows have valid dates and which don’t. If you try to convert an invalid string to DATE without checking first, PostgreSQL throws an error and your entire query fails. I’ve learned the hard way that validating data before conversion prevents those frustrating import failures.

You have several strategies: you can try the conversion and catch errors, use regex patterns to pre-validate the format, or create a reusable function that returns true or false. The right approach depends on whether you want to filter out bad data or collect information about what’s invalid.

Quick Answer: Try Cast and Catch Exception

To check if a string is a valid date:

SELECT
    value,
    CASE
        WHEN (value::DATE IS NOT NULL) THEN true
        ELSE false
    END as is_valid_date
FROM (
    VALUES
        ('2026-02-21'),
        ('2026-13-01'),
        ('invalid-date'),
        ('2025-02-15')
) AS t(value)
WHERE (value::DATE IS NOT NULL);

Only valid dates appear in results.

How It Works

PostgreSQL’s type casting is strict. Invalid dates throw errors. We catch them with CASE logic or WHERE filtering.

Different Approaches & When to Use Them

Approach 1: Using WHERE with Cast (Safest)

SELECT value::DATE as parsed_date
FROM dates_table
WHERE (value::DATE IS NOT NULL);

Only returns valid dates.

Approach 2: Function with Error Handling

CREATE OR REPLACE FUNCTION is_valid_date(text)
RETURNS boolean AS $$
BEGIN
    PERFORM $1::DATE;
    RETURN true;
EXCEPTION WHEN others THEN
    RETURN false;
END;
$$ LANGUAGE plpgsql;

SELECT value, is_valid_date(value) as is_date FROM table_name;

Reusable function.

Approach 3: Regex Pattern Check

SELECT
    value,
    value ~ '^\d{4}-\d{2}-\d{2}$' as matches_date_pattern
FROM strings;

Quick pattern check (not exhaustive).

Real-World Example: Data Quality Check

Identify invalid dates in import:

SELECT
    row_id,
    date_str,
    CASE
        WHEN (date_str::DATE IS NOT NULL) THEN 'Valid'
        ELSE 'Invalid - cannot parse'
    END as validation_result
FROM csv_import
WHERE (date_str::DATE IS NOT NULL);

FAQ

Q: What date formats does PostgreSQL recognize? A: ISO format (YYYY-MM-DD), text formats, timestamps. See docs for full list.

Q: How do I check multiple date columns? A: Apply logic to each: (col1::DATE IS NOT NULL) AND (col2::DATE IS NOT NULL).

Q: Performance for checking thousands of rows? A: Fast. Type casting is optimized.

Wrapping Up

Check if a string is a valid date by attempting the cast in a WHERE clause or CASE statement. Invalid dates are filtered out or return false.