How to Convert String to Date in ClickHouse

Galaxy Glossary

How do I convert a string to a Date or DateTime in ClickHouse?

Convert text to ClickHouse Date or DateTime with toDate, toDateTime, parseDateTimeBestEffort, or CAST.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What functions turn a string into Date or DateTime?

Use toDate() for Date, toDateTime() for DateTime, or parseDateTimeBestEffort() when the input format varies. All accept a string literal or column.

How do I cast a column in a SELECT?

Wrap the column in the desired function or use CAST(col AS Date). The result can be aliased for downstream joins or filtering.

Can I store the converted value permanently?

Yes—run ALTER TABLE ... UPDATE to overwrite the original column or insert into a new table with the converted column type.

How do I handle multiple string formats?

Chain if with like patterns or default to parseDateTimeBestEffort(), which recognises ISO-8601, RFC-2822, and common U.S./EU formats automatically.

Why use a specific timezone?

To avoid implicit server-timezone shifts, pass a second argument: toDateTime('2024-05-10 08:30','UTC'). For per-row zones, ensure the column already carries TZ info.

Best practice tips

Store dates as Date or DateTime as early as possible, index partitions on dates, and prefer toDate( toDateTime(...)) instead of string ops in filters.

Why How to Convert String to Date in ClickHouse is important

How to Convert String to Date in ClickHouse Example Usage


-- Create a reporting view with clean dates
CREATE VIEW orders_clean AS
SELECT o.id,
       toDateTime(o.order_date, 'UTC')      AS order_ts,
       c.name                               AS customer_name,
       SUM(oi.quantity * p.price)           AS basket_value
FROM Orders      AS o
JOIN Customers   AS c ON c.id = o.customer_id
JOIN OrderItems  AS oi ON oi.order_id = o.id
JOIN Products    AS p  ON p.id = oi.product_id
GROUP BY o.id, order_ts, customer_name;

How to Convert String to Date in ClickHouse Syntax


-- Convert ISO string to Date
SELECT toDate('2024-05-20');

-- Convert ISO string to DateTime with explicit timezone
SELECT toDateTime('2024-05-20 14:35:10', 'UTC');

-- Parse mixed formats ("2024/05/20", "20-May-2024")
SELECT parseDateTimeBestEffort(order_date_str) AS order_ts
FROM Orders;

-- CAST syntax inside SELECT
SELECT id,
       CAST(order_date_str AS Date) AS order_dt
FROM Orders;

-- Update a table, keeping history
ALTER TABLE Orders UPDATE order_date_str = toDate(order_date_str) WHERE 1;

Common Mistakes

Using toDate() on a full timestamp: This truncates the time part without warning—use toDateTime() or parseDateTimeBestEffort() instead.
Relying on server timezone defaults: Omitting the timezone argument can shift times during DST changes. Always specify the intended zone.