---
title: CANNOT_PARSE_DATETIME ClickHouse error
meta:
  description: Learn how to fix the CANNOT_PARSE_DATETIME error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# CANNOT_PARSE_DATETIME ClickHouse error

{% callout %}
This error usually means ClickHouse can't parse a string as a DateTime value. Check the format of your date/time strings and make sure they match the expected format.
{% /callout %}

The `CANNOT_PARSE_DATETIME` error in ClickHouse (and Tinybird) happens when you try to convert a string to a DateTime value, but the string format doesn't match what ClickHouse expects. This is a common issue when working with date and time data from external sources.

## What causes this error

You'll typically see it when:

* Using `toDateTime()` on strings with unexpected formats
* Working with date strings that don't match ClickHouse's default format
* Importing data with non-standard date/time formats
* Using date functions with incorrectly formatted strings
* Working with timestamps from different time zones

## Example errors

```sql {% title="Fails: invalid date string" %}
SELECT toDateTime('not a date')
```

```sql {% title="Fails: empty string" %}
SELECT toDateTime('')
```

```sql {% title="Fails: invalid date string" %}
SELECT toDateTime('not a date')
```

```sql {% title="Fails: non-date string" %}
SELECT toDateTime('abc')
```

## How to fix it

### Use the correct date format

ClickHouse expects ISO format by default (YYYY-MM-DD HH:MM:SS):

```sql {% title="Fix: use ISO format" %}
SELECT toDateTime('2023-01-01 12:00:00')
```

### Use parseDateTimeBestEffort for flexible parsing

Use `parseDateTimeBestEffort()` for free-form date parsing:

```sql {% title="Fix: use parseDateTimeBestEffort" %}
SELECT parseDateTimeBestEffort('2023/01/01 12:00:00')
```

### Handle date-only strings

For strings that only contain dates, use `toDate()`:

```sql {% title="Fix: use toDate for date-only strings" %}
SELECT toDate('2023-01-01')  -- Works with YYYY-MM-DD format
```

{% callout type="tip" %}
`toDateTime('2023-01-01')` is also valid - ClickHouse parses YYYY-MM-DD as midnight.
{% /callout %}

### Use parseDateTime for custom formats

Use `parseDateTime()` with format strings:

```sql {% title="Fix: use parseDateTime" %}
SELECT parseDateTime('2023/01/01 12:00:00', '%Y/%m/%d %H:%M:%S')
```

## Common patterns and solutions

### Standard ISO format

The most reliable format is ISO 8601:

```sql {% title="ISO format examples" %}
SELECT toDateTime('2023-01-01 12:00:00')  -- YYYY-MM-DD HH:MM:SS
SELECT toDateTime('2023-01-01T12:00:00')  -- ISO 8601 with T
SELECT toDateTime('2023-01-01 12:00:00.123')  -- With milliseconds
```

### Custom format strings

Use format strings with parseDateTime for non-standard formats:

```sql {% title="Custom format examples" %}
SELECT parseDateTime('01/01/2023 12:00 PM', '%m/%d/%Y %I:%M %p')
SELECT parseDateTime('2023.01.01 12:00:00', '%Y.%m.%d %H:%M:%S')
SELECT parseDateTime('01-01-2023 12:00', '%d-%m-%Y %H:%M')
```

### Unix timestamps

For Unix timestamps, use `fromUnixTimestamp()`:

```sql {% title="Unix timestamp conversion" %}
SELECT fromUnixTimestamp(1672531200)  -- Unix timestamp to DateTime
```

### Working with time zones

Handle time zone information:

```sql {% title="Time zone handling" %}
SELECT toDateTime('2023-01-01 12:00:00', 'UTC')
SELECT parseDateTime('2023-01-01 12:00:00 +05:00', '%Y-%m-%d %H:%M:%S %z')
```

## Advanced solutions

### Handling various date formats

Create robust parsing for multiple formats:

```sql {% title="Multiple format handling" %}
SELECT CASE
    WHEN length(date_string) = 10 THEN toDate(date_string)
    WHEN length(date_string) = 19 THEN toDateTime(date_string)
    WHEN length(date_string) = 23 THEN toDateTime(date_string, 'YYYY-MM-DD HH:MM:SS.SSS')
    ELSE NULL
END as parsed_date
FROM events
```

### Working with JSON date strings

Parse dates from JSON data:

```sql {% title="JSON date parsing" %}
SELECT
    toDateTime(JSONExtractString(data, 'timestamp')) as event_time,
    toDate(JSONExtractString(data, 'date')) as event_date
FROM events
```

### Error handling with try functions

Use `try` functions to handle parsing errors gracefully:

```sql {% title="Error handling with try" %}
SELECT
    try(toDateTime(timestamp_string)) as parsed_time,
    try(toDate(date_string)) as parsed_date
FROM events
```

## Format string reference

Common format specifiers for `toDateTime()`:

* `YYYY` - 4-digit year
* `MM` - 2-digit month (01-12)
* `DD` - 2-digit day (01-31)
* `HH` - 2-digit hour (00-23)
* `MM` - 2-digit minute (00-59)
* `SS` - 2-digit second (00-59)
* `SSS` - 3-digit millisecond (000-999)

## Tinybird-specific notes

In Tinybird, this error often occurs when:

* Ingesting data with non-standard date formats
* Working with timestamps from different sources
* Using Materialized Views with date/time columns
* Processing JSON data with embedded timestamps

To debug in Tinybird:

1. Check your Data Source schema for date/time column types
2. Use Schema Hints to specify date formats
3. Add explicit date parsing in your Pipes
4. Validate incoming date formats in Data Sources

## Best practices

### Standardize date formats

* Use ISO 8601 format (YYYY-MM-DD HH:MM:SS) when possible
* Specify time zones explicitly
* Handle milliseconds consistently
* Use appropriate precision for your use case

### Error handling

* Use `try()` functions for robust parsing
* Validate date strings before processing
* Provide fallback values for invalid dates
* Log parsing errors for debugging

## See also

* [ClickHouse Date and Time Functions](/sql-reference/functions/date-time-functions)
* [Working with Date and Time](/sql-reference/functions-troubleshooting/date-and-time)
* [Common function errors](/sql-reference/clickhouse-errors/CANNOT_PARSE_DATETIME)