How to Format Dates in Snowflake

How do I format and convert dates in Snowflake SQL?

DATE and DATETIME values can be reformatted in Snowflake using TO_CHAR, TO_DATE, CAST, and FORMAT parameters to display or convert dates in any needed pattern.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the quickest way to format a date in Snowflake?

Use TO_CHAR(date_expression, 'format') to turn a DATE, TIME, or TIMESTAMP into a string. Supply any valid Snowflake format mask, such as 'YYYY-MM-DD' or 'DD Mon YYYY'.

How do I convert strings to dates safely?

Wrap the string with TO_DATE('2024-06-15', 'YYYY-MM-DD') or use CAST('15/06/2024' AS DATE FORMAT 'DD/MM/YYYY'). Always pass the correct mask to avoid implicit-conversion errors.

Which date format tokens are the most common?

YYYY=four-digit year, YY=two-digit year, MM=month number, MON=month abbreviation, DD=day of month, HH24=24-hour, MI=minute, SS=second, TZ=time-zone.

How can I format order dates for customer emails?

Format the column directly in the SELECT: SELECT TO_CHAR(order_date, 'FMDay, DD Mon YYYY') AS friendly_date FROM Orders; The FM prefix removes leading zeros and padding spaces.

How do I keep ISO-8601 formatting?

Supply the mask 'YYYY-MM-DD"T"HH24:MI:SS"Z"' to TO_CHAR. This is ideal when exporting data to APIs expecting strict ISO strings.

Can I localize dates automatically?

Snowflake supports the ALTER SESSION SET TIMEZONE and ALTER SESSION SET DATE_OUTPUT_FORMAT parameters. Set these before running queries to apply organization-wide formatting rules.

Best practices for consistent date formatting?

Always specify an explicit mask. Store dates as DATE/TIMESTAMP, never pre-formatted strings. Use TO_CHAR only in the presentation layer, and document masks in shared collections.

Why How to Format Dates in Snowflake is important

How to Format Dates in Snowflake Example Usage


-- Send customer-friendly order confirmation dates
SELECT c.name,
       TO_CHAR(o.order_date, 'FMDay, DD Mon YYYY') AS readable_date,
       o.total_amount
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.id = 42;

How to Format Dates in Snowflake Syntax


TO_CHAR(<date_or_timestamp>, '<format_mask>')
TO_DATE(<string>, '<format_mask>')
CAST(<string> AS DATE FORMAT '<format_mask>')

-- E-commerce examples
-- Display order date as "2024-06-15"
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted
FROM Orders;

-- Convert imported CSV string to DATE
SELECT TO_DATE('15/06/2024', 'DD/MM/YYYY') AS order_date;

Common Mistakes

Relying on default session formats: Without explicit masks, TO_CHAR may inherit session settings, leading to inconsistent exports. Fix by always passing a format string.
Casting string to DATE without FORMAT clause: CAST('06/15/2024' AS DATE) may flip month/day in non-US sessions. Always include FORMAT 'MM/DD/YYYY' or use TO_DATE with a mask.