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/

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

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. For example, subtracting from two dates, you get the number of days between the two dates.

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

Adding or subtracting a number from a date adds or subtracting several days from the date. Notice that the second column uses “Due Date” as an alias, but Redshift uses the alias again in the SELECT statement for column four to come up with the discount.

The Add_Months command adds months to a date. Below, we are using a great technique that adds one year. We then show an even better technique to add five years to date. Of course, we could have gotten five years by using 60 (months), but why calculate in your mind when the system will do it for you?

The DATEADD and ADD_MONTHS functions are similar, but they handle the end of the month differently.

You can use the TO_CHAR function to format dates.

The below example shows how robust the EXTRACT command is at extracting portions of a date, time, or timestamp.

You can use the to_char extension to extract portions of a date.

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

MONTHS_BETWEEN returns the number of months between two DATE or TIMESTAMP values.

NEXT_DAY returns the date of the first specified day of the week (DOW) after the input date.

Posted in Blog