---
title: "5 ways to parse Dates and DateTimes in ClickHouse"
description: "ClickHouse provides several ways to convert dates and datetimes from Unix timestamps, packed numeric values, known-format strings, mixed inputs, and file data. The examples use fromUnixTimestamp and its millisecond and microsecond variants, YYYYMMDDToDate and YYYYMMDDhhmmssToDateTime, plus parseDateTime or parseDateTimeInJodaSyntax when the input format is known. For unknown or mixed formats, parseDateTimeBestEffort and its DateTime64 counterpart accept inputs such as ISO 8601 strings, dates, and timestamp strings; invalid values otherwise raise an exception, while OrNull returns NULL and OrZero returns January 1, 1970 at midnight. The post also shows direct ::DateTime casting, controlled by cast_string_to_date_time_mode: basic supports standard forms, whereas best_effort broadens parsing but still throws for completely invalid dates. An inline or session setting lets the same casting approach parse rows from a CSV file."
---

# 5 ways to parse Dates and DateTimes in ClickHouse

[Clickhouse](https://yomu.fyi/company/clickhouse) · Mark Needham · Mar 12, 2026

**Type:** Tutorial

## Summary

ClickHouse provides several ways to convert dates and datetimes from Unix timestamps, packed numeric values, known-format strings, mixed inputs, and file data. The examples use fromUnixTimestamp and its millisecond and microsecond variants, YYYYMMDDToDate and YYYYMMDDhhmmssToDateTime, plus parseDateTime or parseDateTimeInJodaSyntax when the input format is known. For unknown or mixed formats, parseDateTimeBestEffort and its DateTime64 counterpart accept inputs such as ISO 8601 strings, dates, and timestamp strings; invalid values otherwise raise an exception, while OrNull returns NULL and OrZero returns January 1, 1970 at midnight. The post also shows direct ::DateTime casting, controlled by cast\_string\_to\_date\_time\_mode: basic supports standard forms, whereas best\_effort broadens parsing but still throws for completely invalid dates. An inline or session setting lets the same casting approach parse rows from a CSV file.

## Context

Dates may arrive as Unix timestamps from event streams, packed numeric values in legacy exports or mainframe flat files, formatted strings from APIs, or mixed values in a file. The post addresses both known and unknown date formats, including invalid input handling.

## Approach / What changed

Use explicit ClickHouse conversion functions for Unix timestamps, packed dates, and known formats; use the parseDateTimeBestEffort family for mixed or unknown formats; or cast strings directly to DateTime after configuring cast\_string\_to\_date\_time\_mode as basic or best\_effort. The examples include NULL and zero fallbacks and CSV input.

## Takeaways

- fromUnixTimestamp returns DateTime, while fromUnixTimestamp64Milli and fromUnixTimestamp64Micro return DateTime64(3) and DateTime64(6), respectively.
- parseDateTimeBestEffort64 throws on completely invalid input; its OrNull variant returns NULL, and its OrZero variant returns January 1, 1970 at midnight.
- cast\_string\_to\_date\_time\_mode defaults to basic; setting it to best\_effort enables broader formats, but completely invalid dates still cause an exception.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines)

- Source: [Clickhouse](https://clickhouse.com/blog/parsing-dates-datetimes)
- Source URL: https://clickhouse.com/blog/parsing-dates-datetimes
- Ingested by Yomu: 2026-08-28T01:22:08.859Z

[Read original post](https://clickhouse.com/blog/parsing-dates-datetimes)