Implied Extract of Day, Month and Year Teradata

Compatibility: Teradata Extension

Although the EXTRACT works great and it is ANSI compliant, it is a function. Therefore, it must be executed and the parameters passed to it to identify the desired portion as data. Then, it must pass back the answer. As a result, there is additional overhead processing required to use it.

It was mentioned earlier that Teradata stores a date as an integer and therefore allows math operations to be performed on a date. The syntax for implied extract:

The following SELECT uses math to extract the three portions of Mike's literal birthday:


1 Row ReturnedSELECT uses math to extract the three portions

Remember that the date is stored as yyymmdd. The literal values are used here to provide a date of Oct. 1, 2001. The day portion is obtained here by making the dd portion (last 2 digits) the remainder from the MOD 100. The month portion is obtained by dividing by 100 to eliminate the dd to leave the mm (new last 2 digits) portion the remainder of the MOD 100. The year portion is the trickiest. Since it is stored as yyy (yyyy – 1900), we must add 1900 to the stored value to convert it back to the yyyy format. What do you suppose the EXTRACT function does? Same thing.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics