I can teach you, Yellowbrick! I have never seen analytic queries perform faster than on a Yellowbrick Data Warehouse. Today we are going to learn how to use dates. I will teach you date fundamentals and show you some clever tricks that will help you master dates.
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 are adding or subtracting 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 you subtract 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.
Webinar Recording of Load and Go with Yellowbrick Data Warehouse and Nexus
Here is the link to the webinar recording: https://www.yellowbrick.com/media/load-and-go-with-yellowbrick-and-nexus-webinar
Yellowbrick Data Warehouse is a modern, standards-based MPP analytic database that shatters ceilings on price/performance, achieving 100X performance versus legacy alternatives at a fraction of their cost. With its unified hybrid cloud architecture, applications can deploy anywhere with the same data and performance, de-risking cloud migrations and unlocking multi-cloud innovation.
Coffing Data Warehousing’s Nexus Desktop and Nexus Server is the perfect combination for a remote workforce to manage a hybrid-cloud environment. From the user’s desktop, users can query every system, migrate to every system, and join data across every database. Users set up everything with the click of a button, but the Nexus Server’s job is to execute the work. The Nexus Desktop and Server ensure that all processing is done on a high-speed network so that users can work from any location. We are migrating millions of rows per second to Yellowbrick.
Why not try out a Proof of Concept (POC) of Yellowbrick and Nexus. The POC is free for 30 days. Please contact me for more information or check out www.Yellowbrick.com.
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.