Snowflake is a cloud data platform that supports various data-related tasks including data warehousing, data lakes, and data engineering. When working with date and timestamp data types in Snowflake, you can leverage the TO_VARCHAR function to convert dates or timestamps into a string format of your choice.
Here's how you can change the date format in Snowflake:
Tutorial to Change Format Date on Snowflake, using Chrome Extension
CastorDoc developed a Chrome Extension to help you solve formatting problems such as "How to change date format in Snowflake?". Simply attach the relevant data tables & CastorDoc helps you build queries that are relevant to your business, enriched with technical context on data tables. Check it out now.
Using the TO_VARCHAR Function with Date Format Patterns
The TO_VARCHAR function can be used to convert dates and timestamps to string values based on the provided date format pattern.
Suppose you have a date 2023-09-12 and you want to change its format to DD-MMM-YYYY (e.g., 12-SEP-2023):
Common Date Format Patterns
Here are some commonly used date format patterns:
- YYYY-MM-DD: Standard date format (e.g., 2023-09-12).
- DD-MMM-YYYY: Day abbreviation month year (e.g., 12-SEP-2023).
- MM/DD/YYYY: U.S. date format (e.g., 09/12/2023).
- DD/MM/YYYY: European date format (e.g., 12/09/2023).
- HH24:MI:SS: 24-hour time format (e.g., 23:59:59).
You can combine date and time patterns as needed, and also use other patterns available in Snowflake's documentation.
- YYYY-MM-DD (Standard Date Format)
Convert a date to the standard YYYY-MM-DD format.
- DD-MMM-YYYY (Day Abbreviation Month Year)
Convert a date to the DD-MMM-YYYY format, such as 12-SEP-2023.
- MM/DD/YYYY (U.S. Date Format)
Convert a date to the U.S. style MM/DD/YYYY format.
- HH24:MI:SS (24-hour Time Format)
Convert a timestamp to the 24-hour time format.
- Day of the Week
Get the day of the week (e.g., Monday, Tuesday, etc.) from a date.
- Combining Date and Time FormatsYou can combine date and time patterns to get combined output. For instance, to get the format YYYY-MM-DD HH24:MI:SS:
- Converting a String to a Date and Changing its Format
If you have a date in a string format and need to change its format, you can first convert it to a date or timestamp, and then change its format.
Example: Convert a date in the format DD/MM/YYYY to MM-DD-YYYY.
Remember, the date format patterns mentioned above are some of the most common ones, but Snowflake supports a wide variety of date format patterns. Always refer to Snowflake's official documentation for a comprehensive list and details.
- Ensure that the date or timestamp values you're working with are indeed of the DATE or TIMESTAMP data types. If not, you might need to first convert them using TO_DATE or CAST.
- If you're dealing with a string representation of a date and need to change its format, you'll first need to convert the string to a date or timestamp, and then back to a string with the desired format.
Remember to always test your queries on a subset of your data or in a development environment to ensure they work as expected.
You might also like
Step-by-by guide to use dbt in Snowflake data warehouse. We explain how to set up a dbt project, Group, Warehouse and Service Account in Snowflake.
Connect to Snowflake using the conn object and the cur.execute method from Python. Sample code provided.
“[I like] The easy to use interface and the speed of finding the relevant assets that you're looking for in your database. I also really enjoy the score given to each table, [which] lets you prioritize the results of your queries by how often certain data is used.” - Michal P., Head of Data