Saturday, September 11, 2010

Teradata TIMESTAMP tips & tricks - Part 2

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
from sys_calendar.calendar 
where extract(year from add_months( date, -1)) = year_of_calendar
and
extract(month from add_months(date,-1)) = month_of_year and day_of_week = 6
) b
where
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