Snowflake – Formatting Dates with the TO_CHAR Function

You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Snowflake uses the TO_CHAR command to transform raw date or timestamp data to format it as you want it presented.

For example, TO_CHAR (current_date, ‘yyyy-mm-dd’) would format the current date as ‘YYYY-MM-DD’. You can tailor the output by including various format specifiers for years, months, days, hours, minutes, seconds, and more.

I will start with the basics, give you all the components, show you some top standards worldwide, and then show you 224 examples.

Below is a picture of a query to see the syntax and how the TO_CHAR function is implemented.

I bet you didn’t know you could use the TO_CHAR function in the WHERE and AND clause to filter rows. The query below only returns rows where the customer_timestamp has a month of December, a day of Saturday, a minute of 55, a second of 34, and microseconds = 000000. Also notice we use the alias DOW in the SELECT list and again in the WHERE clause.

Below is a query you can run on a Snowflake system to see the main components to format a date using the TO_CHAR command. You will also see a table showing the query components and the results.

SELECT
CURRENT_DATE AS current_date,
TO_CHAR(CURRENT_DATE, ‘YYYY’) AS year_four_digits,
TO_CHAR(CURRENT_DATE, ‘YY’) AS year_last_two_digits,
TO_CHAR(CURRENT_DATE, ‘MM’) AS month,
TO_CHAR(CURRENT_DATE, ‘Mon’) AS abbreviated month,    
TO_CHAR(CURRENT_DATE, ‘MMMM’) AS full_month,
TO_CHAR(CURRENT_DATE, ‘DD’) AS day_with_zeros,
TO_CHAR(CURRENT_DATE, ‘DY’) AS abbreviated_day_of_the_week,
TO_CHAR(CURRENT_DATE, ‘DYDY’) AS full_day_of_the_week,
TO_CHAR(CURRENT_DATE, ‘HH12’) AS hour_12_no_zeros,
TO_CHAR(CURRENT_DATE, ‘HH:MI:SS’) AS hour_12_with_zeros,
TO_CHAR(CURRENT_DATE, ‘HH24’) AS hour_24_no_zeros,
TO_CHAR(CURRENT_DATE, ‘HH24:MI:SS’) AS hour_24_with_zeros,
TO_CHAR(CURRENT_DATE, ‘MI’) AS minute,
TO_CHAR(CURRENT_DATE, ‘SS’) AS second;

Below is a table to show the query and the results.

Here are the 15 most common date format standards used worldwide, followed by a table with the syntax and the results.

‘YYYY-MM-DD’ (ISO 8601): Example: 2024-01-13
ISO 8601 standard date format, widely adopted globally for its unambiguous representation.

‘DD/MM/YYYY’ (European): Example: 13/01/2024
Commonly used in European countries, featuring day, month, and year in the specified order.

‘MM/DD/YYYY’ (United States): Example: 01/13/2024
Standard date format in the United States, with month, day, and year in the specified order.

‘YYYY年MM月DD日’ (Japanese): Example: 2024年01月13日
Japanese date format using characters for year, month, and day.

‘DD.MM.YYYY’ (Russian): Example: 13.01.2024
Common in Russia, using dots as separators for day, month, and year.

‘YYYY/MM/DD’ (Chinese): Example: 2024/01/13
Standard date format in China, featuring year, month, and day in the specified order.

‘DD-MM-YYYY’ (India): Example: 13-01-2024
Common in India, using hyphens as separators for day, month, and year.

‘DD/MM/YYYY’ (Australian): Example: 13/01/2024
Similar to the European format, widely used in Australia.

‘YYYY년 MM월 DD일’ (South Korean): Example: 2024년 01월 13일
Korean date format using characters for year, month, and day.

‘YYYY-MM-DD’ (Global): Example: 2024-01-13
A concise global format uses hyphens as year, month, and day separators.

‘DD/MM/YYYY’ (Middle East): Example: 13/01/2024
Common in Middle Eastern countries, featuring day, month, and year.

‘MM/DD/YYYY’ (Canada): Example: 01/13/2024
Similar to the U.S. format, it is widely used in Canada.

‘YYYY.MM.DD’ (Global): Example: 2024.01.13
A concise global format using dots as separators for year, month, and day.

‘DD-MM-YYYY’ (South African): Example: 13-01-2024
Common in South Africa, hyphens are used as separators for day, month, and year.

‘YYYY/MM/DD’ (Taiwanese): Example: 2024/01/13
Taiwanese date format featuring year, month, and day in the specified order.

The table below shows the top 15 date format standards worldwide and the query results.

Below is a table with 224 examples of Snowflake date_format examples.