ClickHouse: How to convert integer after toYYYYMMDD back to DateTime?

Question

In ClickHouse toYYYYMMDD returns integer with year/month/day set positionally.

SELECT toYYYYMMDD(now())

┌─toYYYYMMDD(now())─┐
│          20211112 │
└───────────────────┘

How to convert this integer back to Date type?

Answer 1

You can try parseDateTimeBestEffort: https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#parsedatetimebesteffort

SELECT parseDateTimeBestEffort(toString(20211112))


┌─parseDateTimeBestEffort(toString(20211112))─┐
│                         2021-11-12 00:00:00 │
└─────────────────────────────────────────────┘