---
title: Date and time functions troubleshooting
meta:
  description: Common issues and solutions when working with date and time functions in ClickHouse and Tinybird.
---

# Date and time functions troubleshooting

Common issues and solutions when working with date and time functions in ClickHouse and Tinybird.

## Common errors

### ILLEGAL_TYPE_OF_ARGUMENT

**Error**: `ILLEGAL_TYPE_OF_ARGUMENT: Argument 1 for function toDate must be String, Date, Date32, DateTime, DateTime64, or UInt16, UInt32, UInt64, Int16, Int32, Int64, but got UInt8`

**Cause**: Passing wrong data type to date functions

**Solution**: Cast the value to the expected type
```sql
-- Wrong
SELECT toDate(column_with_uint8)

-- Correct
SELECT toDate(toString(column_with_uint8))
```

### CANNOT_PARSE_DATETIME

**Error**: `CANNOT_PARSE_DATETIME: Cannot parse DateTime from string "invalid_date"`

**Cause**: Invalid date format or string

**Solution**: Use proper date format or handle invalid dates
```sql
-- Handle invalid dates
SELECT toDateOrNull(date_string) as safe_date
```

## Type edge cases

### Working with timestamps

**Issue**: Mixing DateTime and DateTime64 types

**Solution**: Use consistent types
```sql
-- Convert to same precision
SELECT toDateTime64(timestamp_column, 3) as consistent_timestamp
```

### Timezone handling

**Issue**: Unexpected timezone conversions

**Solution**: Be explicit about timezones
```sql
-- Specify timezone explicitly
SELECT toDateTime('2023-01-01 12:00:00', 'UTC') as utc_time
```

## Usage patterns that break Pipes

### Date arithmetic in aggregations

**Issue**: Date arithmetic can cause performance issues in Pipes

**Solution**: Pre-calculate date parts when possible
```sql
-- Instead of calculating in aggregation
SELECT dateAdd(day, -7, toDate(timestamp)) as week_ago

-- Pre-calculate in data source
SELECT timestamp, toDate(timestamp) as date, dateAdd(day, -7, toDate(timestamp)) as week_ago
```

### Complex date formatting

**Issue**: Multiple date function calls in single query

**Solution**: Use intermediate columns
```sql
-- Break down complex date operations
WITH date_parts AS (
  SELECT
    timestamp,
    toDate(timestamp) as date,
    toYear(timestamp) as year,
    toMonth(timestamp) as month
  FROM events
)
SELECT * FROM date_parts
```

## Sample fixes

### Fixing date parsing issues

```sql
-- Problem: Inconsistent date formats
SELECT toDate(date_string) FROM events

-- Solution: Handle multiple formats
SELECT
  CASE
    WHEN toDateOrNull(date_string) IS NOT NULL THEN toDate(date_string)
    WHEN toDateOrNull(parseDateTimeBestEffort(date_string)) IS NOT NULL
      THEN toDate(parseDateTimeBestEffort(date_string))
    ELSE NULL
  END as parsed_date
FROM events
```

### Working with Unix timestamps

```sql
-- Problem: Unix timestamp as integer
SELECT toDateTime(timestamp_int) FROM events

-- Solution: Convert properly
SELECT fromUnixTimestamp(timestamp_int) as datetime
FROM events
```

## Best practices

1. **Always validate date inputs** - Use `toDateOrNull()` instead of `toDate()`
2. **Be explicit about timezones** - Specify timezone in date functions
3. **Use consistent date types** - Avoid mixing DateTime and DateTime64
4. **Pre-calculate date parts** - Calculate date components in data sources when possible
5. **Handle edge cases** - Account for invalid dates and timezone conversions

## Related documentation

- [Date and time functions](/sql-reference/functions/date-time-functions)
- [Data types](/sql-reference/data-types)
- [Common error patterns](/forward/dev-reference/common-error-patterns)