Formatting Dates on Vertica

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/

Would you like to format dates on a Vertica system? Vertica uses the TO_CHAR command to transform raw date or timestamp data to your desired format. This blog will brilliantly break down and explain everything about formatting a Vertica date so you can choose what works best for you.

I will start with the basics, give you all the elements so you can put together the format you want, show you some top standards worldwide from G20 countries, provide you with another table with the most popular formats per industry in the United States, and then finally, provide you with a table that has over 3,000 examples.

Below is a picture of two queries to see the syntax of the TO_CHAR function. I am also providing you with a short cheat sheet to show the major TO_CHAR components.

YYYY: Four-digit year.
YY: Two-digit year.
MM: Month (01-12).
DD: Day of the month (01-31) with leading zero.
Month: Name of month capitalized and padded up to nine characters.
Mon: Abbreviated month name (e.g., Jan, Feb).
CC: Century.
DAY: Name of the day of the week in all caps.
Dy: Abbreviated day of the week capitalized (e.g., Sun, Mon).
Day: Name of the day of the week capitalized.

I bet you didn’t know you could use the TO_CHAR function in the WHERE and AND clauses 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 54, a second of 34, and the first three digits of the microsecond (millisecond) is 000. Also notice we use the alias dow in the SELECT list and again in the WHERE clause.

Below is a table showing the elements or components to format a date using the TO_CHAR command.

Below is a table with the most popular formats worldwide from G20 countries to give examples of different standards.

Here is a table with Vertica format examples from industries in the United States you might want to consider.

Here is a table with 3,000 examples of Vertica date formats I tested so you know they will work.