Saturday, September 11, 2010

Teradata TIMESTAMP tips & tricks - Part 2

This is continuation of following topic

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)) ;


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
from sys_calendar.calendar 
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
) b
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 */

No comments:

Post a Comment