Formatting Dates in Snowflake: A Guide to Flexible Customization

Dates are an integral part of nearly any dataset. Understanding how to manipulate and display them meaningfully is crucial for extracting insights. Snowflake, a robust cloud data warehouse, offers the flexibility to format dates according to your specific analysis or visualization needs. Let’s explore how you can master date formatting within Snowflake.

Understanding Snowflake’s Date Data Type

Snowflake has a dedicated DATE data type. It stores only the date components (year, month, day) without including any time information. Here are some common ways to input dates into Snowflake:

• Standard Format: YYYY-MM-DD (e.g., 2023-09-12)
• Variations: Slashes, dashes, or other separators can be used (e.g., 2023/09/12)
• Verbose Text: Day, Abbreviated/Full Month, Year (e.g., 12-Sep-2023)

Critical Functions for Date Formatting

1. TO_DATE()
2. Use the TO_DATE function to convert string representations of dates into the DATE data type. It’s incredibly versatile:
3. SQL
4. SELECT TO_DATE(‘2023-09-12’, ‘YYYY-MM-DD’) AS formatted_date; 
5. SELECT TO_DATE(’12-Sep-2023′, ‘DD-MON-YYYY’) AS formatted_date;
6. Use code with caution.
7. content_copy
8. TO_VARCHAR()
9. This function is essential for customizing the output display of your dates. Use it in conjunction with format specifiers:
10. SQL
11. SELECT TO_VARCHAR(current_date(), ‘YYYY-MM-DD’) AS today_standard,
12.        TO_VARCHAR(current_date(), ‘DD MMMM YYYY’) AS today_verbose;
13. Use code with caution.
14. content_copy

Common Format Specifiers

SpecifierDescriptionExample

Y Four-digit year 2023

MM Two-digit month 09

DD Two-digit day 12

MON Abbreviated month name SEP

MONTH Full month name September

DY Abbreviated day of the week Tue

DAY Full day of the week Tuesday

drive_spreadsheetExport to Sheets

Examples

• US Format: MM/DD/YYYY
• SQL
• SELECT TO_VARCHAR(TO_DATE(‘2023-09-12’, ‘YYYY-MM-DD’), ‘MM/DD/YY’)
• Use code with caution.
• content_copy
• European Format: DD/MM/YYYY
• SQL
• SELECT TO_VARCHAR(TO_DATE(‘2023-09-12’, ‘YYYY-MM-DD’), ‘DD/MM/YYYY’)
• Use code with caution.
• content_copy
• Verbose with Timestamp: ‘Day, Month DD, YYYY – HH:MI: SS AM/PM’
• SQL
• SELECT TO_VARCHAR(current_timestamp(), ‘DAY, MONTH DD, YYYY – HH12:MI:SS AM’)
• Use code with caution.
• content_copy

Beyond the Basics

• DATE_OUTPUT_FORMAT Parameter: Use ALTER SESSION SET DATE_OUTPUT_FORMAT to change your session’s default display format temporarily.
• Timezones: Explore functions like CONVERT_TIMEZONE if dealing with data across timezones.

Remember: Snowflake offers extensive customization for date formatting. Refer to the official Snowflake documentation for a complete list of format specifiers and more in-depth examples.