DATE Processing - Teradata

Much of the time spent processing dates is dedicated to storage and reference. Yet, there are times that one date yields or derives a second date. For instance, once a bill has been sent to a customer, the expectation is that payment comes 60 days later. The challenge becomes the correct calculation of the exact due date.

Since Teradata stores the date as an INTEGER, it allows simple and complex mathematics to calculate new dates from dates. The next SELECT operation uses the Teradata date arithmetic and DATEFORM=INTEGERDATE to show the month and day of the payment due date in 60 days:

SELECT Order_date+60 (Title 'Due Date') ,Order_date ,Order_total (FORMAT '$$$$,$$$.99') FROM Order_table WHERE Order_date > '98/12/31' ;
4 Rows ReturnedDATE Processing

Besides a due date, the SQL can also calculate a discount period date 10 days prior to the payment due date using the alias name:

SELECT Order_date ,Order_date +60 AS Due_Date ,Order_total (FORMAT '$$$$,$$$.99') ,Due_date -10 (Title 'Discount Date') ,Order_total*.98 (FORMAT '$$$$,$$$.99', Title 'Discounted') FROM Order_table WHERE Order_date > 981231 ;
4 Rows ReturnedDATE Processing

In the above example, it was demonstrated that a DATE + or − an INTEGER results in a new date (date { + | − } integer = date). However, it probably does not make a lot of sense to multiply or divide a date by a number.

As seen earlier in this chapter, the stored format of the date is YYYMMDD. Since DD is the lowest component, the 60 being added to the order date in the above SELECT is assumed to be days. The system is smart enough to know that it is dealing with a date. Therefore, it is smart enough to know that a normal year contains 365 days.
The associative properties of math tell us that equations can be rearranged and still be valid. Therefore, a DATE – a DATE results in an INTEGER (date +|− date = integer). This INTEGER represents the number of days between the dates.

math operation on dates

This SELECT uses this principal to display the number of days I was alive on my last birthday:

sel (1001001(date)) - (521001(date)) (Title 'Mike"s Age In Days');
1 Row Returned

Mike's Age in Days


The above example subtracted one of my birthdays (October 1, 2000) with my actual birthday in 1952. Notice how awful an age looks in days! More importantly, notice how I slipped it into the Title the fact that you can use two single quotes to store or display a literal single quote in a character string.

As mentioned above, an age in days looks awful and that is probably why we do not use that format. I am not ready to tell someone I am just a little over 17000. Instead, we think about ages in years. To convert the days to years, again math can be used as seen in the following SELECT:

sel ((1001001(date)) - (521001(date)))/365 (Title 'Mike"s Age In Years');
1 Row Returned

Mike's Age in Years


Wow! I feel so much younger now. This is where division begins to make sense, but remember, the INTEGER is not a DATE. At the same time, it assumes that all years have 365 days. It only does the math operations specified in the SQL statement.

Now, what day was he born? The next SELECT uses the concatenation, date arithmetic and a blank TITLE to produce the desired output:

sel 'Mike was born on day ' || ((521001(date)) - (101(date))) MOD 7 (TITLE "); 1 Row Returned
Mike was born on day 2

The above subtraction results in the number of days between the two dates. Then, the MOD 7 divides by 7 to get rid of the number of weeks and results in the remainder. A MOD 7 can only result in values 0 thru 6 (always 1 less than the MOD operator). Since January 1, 1900 ( 101(date) ) is a Monday, Mike was born on a Wednesday.

day of the week

The following SELECT uses a year's worth of days to derive a new date that is 365 days away:

SELECT Order_date ,Order_date +365 (Title 'Year Later Date') ,Order_total (FORMAT '$$$$,$$$.99') FROM Order_table ORDER BY 1 ;
5 Rows ReturnedSELECT uses a year's worth of days to derive a new date that is 365 days away

In the above, the year 1999 was not a leap year. Therefore, the value of 365 is used. Likewise, had the beginning year been 2000, then 366 needs to be used because it is a Leap Year. Remember, the system is simply doing the math that is indicated in the SQL statement. If a year were always needed, regardless of the number of days, see the ADD_MONTHS function.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics