Date and time manipulation on Teradata

Timestamp

The timestamp data type does not have a format command in V2R4, but V2R5 does have a format commend.  The quickest way to format a date time in V2R4 is:

select cast(current_date as TimeStamp(2)) 

        + ((current_time - time '00:00:00') hour to second);

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.

 

Find the previous Friday from today:

select *  from   sys_calendar.calendar where  day_of_week=6 and calendar_date between date -6 and date;

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;

Extracting Date Components

Select extract(year from foo_date), extract(month from foo_date from db.snafu;

Simple Date Arithmetic

Days difference:

Select date – old_date from foo;

Calculate the Julian date

select current_date (format 'yyyyddd') (char(7));

Adding or Subtracting Months

This is as simple as it gets:

select add_month(date_col,1) from table;

select add_month(date_col,-10) from table;

Calculating Date Intervals

These examples will not work ODBC unless you select type IIA in the ODBC configuration.  These examples will work in Bteq as given.

SELECT

 (CAST(((END_DT(DATE, FORMAT'YYYY-MM-DD'))(CHAR(10)))||' '||

END_TIME AS TIMESTAMP(0)) - CAST(((START_DT(DATE, FORMAT'YYYY-MM-DD'))(CHAR(10)))||' '||

START_TIME AS TIMESTAMP(0))) day(3) TO second AS TIMEDIFF

FROM whatever;