Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use to_date Function in Snowflake: Syntax and Examples

In Snowflake, use the to_date function to convert a string or timestamp to a date type. The syntax is to_date(expression, format), where expression is the input and format is optional to specify the date pattern.
📐

Syntax

The to_date function converts a string or timestamp to a date. It has two parts:

  • expression: The value to convert, like a string or timestamp.
  • format (optional): The pattern describing the date format in the string, like 'YYYY-MM-DD'.

If you omit format, Snowflake tries to guess the date format.

sql
to_date(expression, format)
💻

Example

This example shows converting a string to a date using to_date with and without a format string.

sql
SELECT 
  to_date('2024-06-15') AS date_default_format,
  to_date('15/06/2024', 'DD/MM/YYYY') AS date_custom_format,
  to_date(current_timestamp) AS date_from_timestamp;
Output
DATE_DEFAULT_FORMAT | DATE_CUSTOM_FORMAT | DATE_FROM_TIMESTAMP --------------------|--------------------|------------------- 2024-06-15 | 2024-06-15 | 2024-06-15
⚠️

Common Pitfalls

Common mistakes when using to_date include:

  • Not matching the format string to the input date string, causing errors or wrong dates.
  • Passing a timestamp string without a format, which may lead to unexpected results.
  • Confusing to_date with to_timestamp when time information is needed.

Always verify your input format matches the format parameter.

sql
/* Wrong: format does not match input */
SELECT to_date('06-15-2024', 'YYYY-MM-DD') AS wrong_date;

/* Right: format matches input */
SELECT to_date('06-15-2024', 'MM-DD-YYYY') AS correct_date;
Output
WRONG_DATE ---------- NULL CORRECT_DATE ------------ 2024-06-15
📊

Quick Reference

ParameterDescriptionExample
expressionString or timestamp to convert'2024-06-15'
formatOptional pattern describing input format'DD/MM/YYYY'
ReturnDate value converted from input'2024-06-15'
✅

Key Takeaways

Use to_date(expression, format) to convert strings or timestamps to dates in Snowflake.
Always match the format string to your input date format to avoid errors.
If format is omitted, Snowflake tries to auto-detect the date format.
to_date returns only the date part, ignoring time information.
Use to_timestamp if you need to keep time details.