Mastering Date Functions on Vertica

Below the video are incredible date functions on Vertica. However, you can first witness a two-minute video that joins a Vertica table with tables from Databricks, Snowflake, Redshift, Azure Synapse, BigQuery, Teradata, Oracle, SQL Server, DB2, Yellowbrick, MySQL, Postgres, Microsoft Access, and Excel in a single federated query.

You can now download our books on Synapse, Redshift, Google BigQuery, DB2, Oracle, Yellowbrick, Vertica, MySQL, Postgres, and more for FREE.

You can download the PDFs from our CoffingDW.com website immediately.

Here is how you do it. Go to CoffingDW.com and click on Books from the top right menu. You will then be in the CoffingDW bookstore, where you will see the Snowflake book. Add the Snowflake book to your cart.

Add any book you want to your cart.

When you check out, you use the coupon FRIEND2023.

You won’t have to enter any credit card information. Just put in your name and email, and the price will be discounted to zero. You can then download the book immediately at no cost to you.

The current_date will return today’s date.  Add or subtract a number from the CURRENT_DATE, and you are adding or subtracting days from the date.  However, if you subtract from two dates, you get the number of days between the two dates.

Below are the keywords you can utilize to get the date, time, or timestamp.  These are reserved words that the system will deliver to you when requested.

The NOW function return value does not change during a transaction. Thus, multiple calls to CURRENT_TIMESTAMP within the transaction return the same timestamp. The TIMEOFDAY function returns the wall clock time as a text string. As a result, TIMEOFDAY results advance during transactions.

Adding or subtracting a number from a date adds or subtracting several days from the date. Also, Vertica uses the alias “Due Date” for the second column, but it uses the alias again in column four for the DISCOUNT column.

The example below uses the Add_Months Command. What you can do with it is add a month or many months to your date columns.  

You can use the functions below to see the day of the month, week, week_ISO, and year.

You can use the TO_CHAR function to format dates.

The Extract command extracts portions of a date, time, or timestamp.

The below example uses the date_part function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes.

The DATEDIFF command takes a datepart and returns the difference between two dates or timestamps.

The NEXT_DAY function returns the date of the first instance of a particular day of the week that follows the specified date. The specified date can be a TIMESTAMP, DATE, or VARCHAR.