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
formatstring 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_datewithto_timestampwhen 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
| Parameter | Description | Example |
|---|---|---|
| expression | String or timestamp to convert | '2024-06-15' |
| format | Optional pattern describing input format | 'DD/MM/YYYY' |
| Return | Date 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.
