IS_DATE

Return true if the input string can be parsed as a valid calendar date.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_DATE(str)

Description

## Overview Returns TRUE if the input string can be parsed as a valid calendar date, FALSE otherwise, and NULL for a NULL input. Use this function to validate incoming date-like strings before casting to DATE, to quarantine badly formatted rows at ingest, or to build conditional transformations that tolerate mixed input formats. ## Validation rules - Primary format is ISO 8601: YYYY-MM-DD with a four-digit year, a two-digit month (01 through 12), and a two-digit day (01 through the last day of the month, accounting for leap years). - Also accepts common variations such as YYYY/MM/DD and YYYYMMDD. - Month and day are range-checked against the calendar, so 2025-02-30 returns FALSE and 2024-02-29 returns TRUE. - Leading and trailing whitespace is rejected; TRIM first if needed. - Does not accept timestamps (date + time) or relative strings such as 'today'. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - No timezone processing is performed. ## Compatibility - Accepts the subset of date formats that the engine's DATE cast also accepts. If IS_DATE returns TRUE, `CAST(str AS DATE)` will succeed.

Parameters

Name	Type	Description
`str`		Specifies the string to test. ISO 8601 (YYYY-MM-DD) is the primary accepted form; common slash and dash-separated patterns are also recognized.

Examples

-- Valid ISO date
SELECT IS_DATE('2025-12-31');  -- true

-- Month out of range
SELECT IS_DATE('2025-13-01');  -- false

-- Non-date string
SELECT IS_DATE('hello');  -- false

-- Leap day valid only in leap years
SELECT IS_DATE('2024-02-29') AS leap_ok, IS_DATE('2023-02-29') AS not_leap;  -- true, false

-- Guard a cast to DATE
SELECT raw_dob,
       CASE WHEN IS_DATE(raw_dob) THEN CAST(raw_dob AS DATE) END AS dob
FROM crm.stage.raw_customers;

-- NULL propagation
SELECT IS_DATE(NULL);  -- NULL

Pitfalls

IS_DATE does not accept timestamp strings. `IS_DATE('2025-12-31 10:00:00')` returns FALSE. Strip the time portion or use a TRY_CAST to TIMESTAMP if you need to validate both.
Ambiguous two-digit year formats are rejected. `IS_DATE('12/31/25')` returns FALSE because the engine cannot disambiguate the century.
Calendar validity is enforced: 2025-02-29 is rejected because 2025 is not a leap year. Do not rely on IS_DATE to pass impossible calendar dates.
A NULL input returns NULL. Filter predicates must account for the NULL case explicitly if you want to exclude missing values.