How to Convert String to Date in Snowflake

Galaxy Glossary

How do I convert a string to date in Snowflake?

Use TO_DATE(), TRY_TO_DATE(), or CAST to reliably turn VARCHAR values into DATEs in Snowflake.

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

Which functions convert a string to DATE?

Snowflake offers three main options: TO_DATE(string [, format]), TRY_TO_DATE(string [, format]), and CAST(string AS DATE). TO_DATE errors when the input does not match the expected format, while TRY_TO_DATE returns NULL, making it safer for messy data.

How do I supply a custom format mask?

Pass the format as the second argument. Example: TO_DATE('31/12/2023','DD/MM/YYYY'). Snowflake supports Oracle-style date masks, including YYYY, MM, DD, HH24, and more.

Can I bulk-convert a column during SELECT?

Yes.Wrap the column in the chosen function. When cleaning an events table you might write: SELECT id, TO_DATE(event_ts,'YYYY-MM-DD') AS event_date FROM raw_events;.

How do I safely load bad data?

Use TRY_TO_DATE so malformed rows evaluate to NULL instead of aborting the query. Afterwards, filter or fix the NULL rows.

What is the best practice for time zones?

Convert strings to TIMESTAMP_NTZ first if they contain time-zone info, then cast to DATE.Example: TO_DATE(TO_TIMESTAMP_NTZ('2023-12-31T22:00:00Z')).

Quick checklist

Pick TRY_TO_DATE for unpredictable feeds
Always state the mask if the string is not ISO-8601
Validate results with a WHERE filter before overwriting tables

Why How to Convert String to Date in Snowflake is important

How to Convert String to Date in Snowflake Example Usage


-- Flag orders loaded with bad date strings
after cleaning
SELECT id,
       TRY_TO_DATE(order_date_txt,'DD Mon YYYY') AS order_dt
FROM   Orders_raw
WHERE  TRY_TO_DATE(order_date_txt,'DD Mon YYYY') IS NULL;

How to Convert String to Date in Snowflake Syntax


-- Basic conversion
TO_DATE(<string_expr> [, <format_mask>])

-- Safe conversion (NULL on failure)
TRY_TO_DATE(<string_expr> [, <format_mask>])

-- ANSI style
CAST(<string_expr> AS DATE)

-- Examples with ecommerce tables
-- 1. Convert varchar column during SELECT
SELECT id,
       TO_DATE(order_date,'YYYY-MM-DD') AS order_dt
FROM   staging_orders;

-- 2. Insert cleaned dates into Orders
INSERT INTO Orders(id, customer_id, order_date, total_amount)
SELECT id, customer_id,
       TO_DATE(order_date_txt,'DD/MM/YYYY'),
       total_amount
FROM   Orders_raw;

Common Mistakes

Omitting the format mask when the input is not ISO-8601. Snowflake guesses incorrectly, raising an error. Fix by supplying an explicit mask.
Using TO_DATE on dirty data feeds, which aborts the query on the first bad row. Switch to TRY_TO_DATE, then handle NULLs.