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 incredible date functions on Yellowbrick. However, even more amazing is the two-minute video that joins a Yellowbrick table with Databricks, Snowflake, Redshift, Azure Synapse, BigQuery, Teradata, Oracle, SQL Server, DB2, Vertica, MySQL, Postgres, Microsoft Access, and Excel in a single federated query.

The first fundamental to know is how to get a date to appear in your answer set. From the picture below, you can see we are using the ANSI standard CURRENT_DATE keyword to get today’s date.

When you add or subtract a number from a date, you add or subtract days.  See the example below.  And yes, Yellowbrick uses an intelligent calendar that recognizes a leap year.

The next important fundamental to know is what happens when subtracting between two dates: you receive the number of days between that period.

The next example shows you how to format your dates to look pretty on the report.  The to_char command turns the date into a character string and allows you to format it for display purposes.  Notice the difference between MON and Mon below.  As you can see, MON displays the month in all capitals, while Mon displays the month with only the first letter capitalized.  Also, notice the slashes and commas go where you place them.

The next example shows you how to extract portions of a date. In our SELECT list, we extract the yr and day and extract the month in our WHERE clause.   The WHERE clause limits the rows we want on our report to only September orders.

The next example displays the day of the week and uses its alias in the WHERE clause.  Below, we are finding all Wednesday orders.

The example below displays the dates of the current_date, first day of the month, the last day of the month, the last day of the previous month, and the first day of the next month.


Posted in Blog and tagged , , , , , , ,