ClickHouse DB::Exception: Cannot parse date value

The "DB::Exception: Cannot parse date value" error in ClickHouse appears when a string value cannot be interpreted as a valid date. The CANNOT_PARSE_DATE error code fires specifically for the Date and Date32 column types (not DateTime -- that has its own error). ClickHouse has strict expectations about date formatting, and anything that deviates from the accepted patterns will trigger this error during INSERT or type conversion.

Impact

When CANNOT_PARSE_DATE fires, the INSERT or query that triggered it fails:

• No rows from the batch are written to the target table.
• Data pipelines that ingest date-heavy data (logs, events, time series) are frequently affected.
• If the source system sends dates in an inconsistent or locale-specific format, this error may appear intermittently, making it harder to track down.

Common Causes

1. Wrong date format -- ClickHouse expects YYYY-MM-DD by default, but the data contains formats like MM/DD/YYYY, DD.MM.YYYY, or YYYYMMDD.
2. Empty strings or null markers -- an empty field or a placeholder like NULL, N/A, or 0000-00-00 in a non-Nullable Date column.
3. Out-of-range dates -- the Date type supports dates from 1970-01-01 to 2149-06-06. The Date32 type extends this to 1900-01-01 through 2299-12-31. Values outside these ranges will fail.
4. Timestamp instead of date -- a full datetime string like 2024-01-15 10:30:00 being inserted into a Date column without prior truncation.
5. Locale-specific formatting -- month names in text (e.g., 15-Jan-2024) or ordinal suffixes (e.g., January 15th, 2024).

Troubleshooting and Resolution Steps

1. Check the expected format. ClickHouse's Date type expects YYYY-MM-DD by default. Confirm what your data actually looks like:

   head -5 /path/to/data.csv
   

2. Use parseDateTimeBestEffort for flexible parsing. If your dates are in various formats, parse them through a more lenient function:

   INSERT INTO my_table
   SELECT parseDateTimeBestEffort(date_string)::Date, other_col
   FROM input('date_string String, other_col String')
   FORMAT CSV
   

3. Handle empty or null values. Make the column Nullable or provide a default:

   ALTER TABLE my_table MODIFY COLUMN my_date Nullable(Date);
   

   Or use input_format_csv_empty_as_default = 1 to substitute the column default for empty fields.

4. Convert non-standard formats explicitly. For MM/DD/YYYY dates:

   SELECT parseDateTime('01/15/2024', '%m/%d/%Y')::Date;
   

   For YYYYMMDD compact dates:

   SELECT parseDateTime('20240115', '%Y%m%d')::Date;
   

5. Check for out-of-range values. Use clickhouse-local to scan for problematic dates:

   clickhouse-local --query="SELECT date_col FROM file('data.csv', CSV, 'date_col String') WHERE toDateOrNull(date_col) IS NULL AND date_col != ''"
   

6. Preprocess dates in your ETL pipeline. Standardize all dates to YYYY-MM-DD format before they reach ClickHouse.

Best Practices

• Standardize date formats to YYYY-MM-DD (ISO 8601) across your entire data pipeline.
• Use Nullable(Date) for columns where date values may legitimately be absent.
• Prefer Date32 over Date if your data may contain dates before 1970 or after 2149.
• Use parseDateTimeBestEffort or parseDateTimeBestEffortOrNull for flexible date parsing when dealing with heterogeneous data sources.
• Validate date ranges in your ETL before they hit ClickHouse, especially when migrating from systems that allow dates like 0000-00-00.

Frequently Asked Questions

Q: What date formats does ClickHouse accept by default?
A: For the Date type, ClickHouse accepts YYYY-MM-DD (e.g., 2024-01-15). It also recognizes some variations like YYYY/MM/DD and compact YYYYMMDD. For anything else, explicit parsing with parseDateTime() or parseDateTimeBestEffort() is needed.

Q: My data has dates like 01/15/2024. How do I import them?
A: Use parseDateTime('01/15/2024', '%m/%d/%Y') to convert the string to a DateTime, then cast to Date. Alternatively, preprocess the data to convert to YYYY-MM-DD format before import.

Q: Can I insert a DateTime string into a Date column?
A: Yes, ClickHouse will truncate the time portion when inserting a YYYY-MM-DD HH:MM:SS string into a Date column. However, non-standard datetime formats may still fail.

Q: What happens with dates before 1970 in a Date column?
A: The Date type cannot represent dates before 1970-01-01. Use Date32 for dates going back to 1900-01-01, or store the value as a String if you need even older dates.

Q: How do I handle mixed date formats in the same column?
A: Use parseDateTimeBestEffortOrNull() which attempts to parse dates in many common formats. For values it cannot parse, it returns NULL, which you can then handle with a COALESCE or default value.