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:

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.

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

Contactez-nous pour en savoir plus

Découvrez ce que les utilisateurs aiment chez CastorDoc
Un outil fantastique pour la découverte de données et la documentation

« J'aime l'interface facile à utiliser et la rapidité avec laquelle vous trouvez les actifs pertinents que vous recherchez dans votre base de données. J'apprécie également beaucoup le score attribué à chaque tableau, qui vous permet de hiérarchiser les résultats de vos requêtes en fonction de la fréquence d'utilisation de certaines données. » - Michal P., Head of Data.