This is continuation of following topic http://www.teradatahelp.com/2010/08/teradata-timestamp-tips.html.
Some more tips which might be useful while working on date and timestamp. (Queries run and verified on V2R6)
Tip 1: Adding and Subtracting Dates in Teradata
select my_date + interval '10' day from my_table
And equally to subtract values from a date:
select my_date - interval '10' day from my_table
Tip 2: Calculating the last day in a given month
select add_months(current_date,1) - extract(day from add_months(current_date,1)) ;
Tip 3: Usage of SYS_CALENDAR.CALENDAR
Teradata provides a handy calendar table for date lookup called sys_calendar.calendar.
Using this table you can calculate useful dates such as weekending dates, quarter ending dates, etc.
example1 : Find the previous Friday from today:
select * from sys_calendar.calendar where day_of_week=6 and calendar_date between date -6 and date;
example2 :Find the first Monday one week after the last Friday of previous month:
select a.calendar_date from sys_calendar.calendar a,
sel max(calendar_date) + 10 as calendar_date
where extract(year from add_months( date, -1)) = year_of_calendar
extract(month from add_months(date,-1)) = month_of_year and day_of_week = 6
a.calendar_date = b.calendar_date;
Tip 4: Adding or Subtracting Months
select add_month(date_col,1) from table; /* adding of month */
select add_month(date_col,-10) from table; /* subtraction of month */