Library: White Papers

Date and Time Manipulation on Teradata

This is a quick reference to using date and time on Teradata. I hope the examples are useful to you.

Timestamp

The timestamp data type does not have a format command in V2R4, but V2R5 does have a format command.The quickest way to format a date 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 with 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;




     select ( (cast( '2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 02:00:00' as timestamp(0))
              )  day(4) to hour );

              Sample Result from BTEQ: 3684 10



     select ( (cast( '2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  day(4) );

              Sample Result from BTEQ: 3684



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  month(4) );

              Sample Result from BTEQ: 121



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  month(3) );

              Sample Result from BTEQ: 121



     select ( (cast( '2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  year(3) TO month );

              Sample Result from BTEQ: 10-01



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  year(2) TO month );

              Sample Result from BTEQ: 10-01



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('1990-12-01 12:00:00' as timestamp(0))
              )  year(4) TO month );

              Sample Result from BTEQ: 10-01



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              )  year(1) TO month );

              Sample Result from BTEQ: 0-01



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              ) DAY(3) TO SECOND );

              Sample Result from BTEQ: 31 00:00:00.000000



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              ) DAY(4) TO SECOND );

              Sample Result from BTEQ: 31 00:00:00.000000



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              ) DAY(3) TO hour );

              Sample Result from BTEQ: 31 00



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              ) DAY(3) TO minute );

              Sample Result from BTEQ: 31 00:00



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-01 12:00:00' as timestamp(0))
              ) DAY(3) TO SECOND );

              Sample Result from BTEQ: 31 00:00:00.000000



     select ( (cast('2001-01-01 12:00:00' as timestamp(0))
                  - cast('2000-12-31 12:00:00' as timestamp(0))
              ) DAY(4) TO SECOND );

              Sample Result from BTEQ: 1 00:00:00.000000

This page was developed from an attachment provided by Jeff Ohlman. The attachment can be found in the Attachment area.