Great Teradata Date Functions To Know
Below the video are great date functions on Teradata. However, you can first witness a two-minute video that joins a Teradata table with tables from Databricks, Snowflake, Redshift, Google BiqQuery, Synapse, Vertica, MySQL, Oracle, SQL Server, DB2, Yellowbrick, Postgres, Microsoft Access, 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 book is even watermarked with your name.
Below are the keywords to get the date, time, or timestamp. These are reserved words that the system will deliver to you when requested.
Add or subtract a number from a date, and you are adding or subtracting days.
When subtracting between two dates, you get the approximate number of days between those dates.
The Smart Calendar works so well because it stores EVERY date in Teradata as something known as an INTEGERDATE. Teradata uses the formula above to store dates, but you will see normal date formats when querying.
Below are some examples of formatting the dates.
When you add or subtract from a Date, you add or subtract days. Notice we use the alias “Due Date” for the second column, but also use “Due Date” again for the fourth column’s calculation on Discount.
Below is the Add_Months Command. What you can do with it is add or subtract a month or many months to your date columns.
Like the Add_Months, the EXTRACT Command is a Temporal or Time-Based Function.
The Extract Temporal Function can extract a portion of a date. As you can see, basic arithmetic accomplishes the same thing.
There is something known as a System Calendar (or, as Teradata calls it, Sys_Calendar.Calendar). Get ready for AWESOME! The System Calendar helps with the handling of advanced date logic.
Tera-Tom was born on a Saturday! It was the first full week of the month, the first full week of the year, and the first quarter!
Below is the perfect example of utilizing the system calendar to join any date field and expand your search options.
Tom Coffing, better known as Tera-Tom, founded Coffing Data Warehousing, where he has been CEO for the past 30 years. Tom has written over 90 books on Architecture, performance tuning, and SQL on all relevant systems. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus, which celebrates 20 years of brilliantly integrating all databases.
