How to change date format in Snowflake?

Leverage the TO_VARCHAR function

How to change date format in Snowflake?

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:

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.

Example:

Suppose you have a date 2023-09-12 and you want to change its format to DD-MMM-YYYY (e.g., 12-SEP-2023):

SELECT TO_VARCHAR(CURRENT_DATE(), 'YYYY-MM-DD');

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.

Examples

  • YYYY-MM-DD (Standard Date Format)
    Convert a date to the standard YYYY-MM-DD format.
SELECT TO_VARCHAR(CURRENT_DATE(), 'YYYY-MM-DD');
  • DD-MMM-YYYY (Day Abbreviation Month Year)
    Convert a date to the DD-MMM-YYYY format, such as 12-SEP-2023.
SELECT TO_VARCHAR(CURRENT_DATE(), 'DD-MMM-YYYY');
  • MM/DD/YYYY (U.S. Date Format)
    Convert a date to the U.S. style MM/DD/YYYY format.
SELECT TO_VARCHAR(CURRENT_DATE(), 'MM/DD/YYYY');
  • HH24:MI:SS (24-hour Time Format)
    Convert a timestamp to the 24-hour time format.
SELECT TO_VARCHAR(CURRENT_TIMESTAMP(), 'HH24:MI:SS');
  • Day of the Week
    Get the day of the week (e.g., Monday, Tuesday, etc.) from a date.
SELECT TO_VARCHAR(CURRENT_DATE(), 'DAY');
  • 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:
SELECT TO_VARCHAR(CURRENT_TIMESTAMP(), '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.
SELECT TO_VARCHAR(TO_DATE('12/09/2023', 'DD/MM/YYYY'), '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.

Considerations

  • 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.

New Release
Share

Get in Touch to Learn More

See Why Users Love CastorDoc

G2 leader badge spring 2023
G2 Users Love us badge
g2 leader badge winter 2023
Castor reviews sourced by G2

Fantastic tool for data discovery and documentation

“[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, Head of Data, Printify