Teradata Date CalculationsTeradata stores date internally as INTEGER values. Dates after 1900-01-01 can be calculated with the following formula:
((year- 1900) * 10000) + (month * 100) + day

Nevertheless, personally, i would avoid above calculation as it is difficult to read and wrong for dates before 1900.

Here are some more useful Teradata Date Calculations:

First day of the month:
CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1)

Last day of the previous month:
CURRENT_DATE – EXTRACT(DAY FROM CURRENT_DATE)

Last day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n + 1) – 1

First day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n)

First day of the year:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -EXTRACT(MONTH FROM CURRENT_DATE) + 1)

Day of week (1-7; 1 = monday, 2 = tuesday, …)
(CURRENT_DATE – DATE ‘0001-01-01’) MOD 7 + 1

All calculations above are using the EXTRACT function.

You definitely should avoid substrings for date calculations. Below are three alternative ways of calculating the first day of a month for any given date (based on a huge test table). As you can see, the consumed CPU Times are very different. While the INTEGER style calculation (the second table row) consumes the least resources, you might prefer to avoid it because of its bad readability.

CPU Time Statement
114,40 thedate – EXTRACT(DAY FROM thedate ) + 1
74,10 thedate  / 100 * 100 + 1 (DATE)
166,50 thedate  – SUBSTRING(thedate  FROM 1 FOR 8) || ’01’ (DATE)

 

Our Reader Score
[Total: 5    Average: 3.4/5]
Some useful Teradata Date Calculations written by Roland Wenzlofsky on June 7, 2015 average rating 3.4/5 - 5 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here