The TeradataForum depends on cookies for its operation. Please refer to our Privacy page for details. By closing this banner or continuing to browse the TeradataForum website, you agree to the use of cookies.

 
Home Page for the TeradataForum
 

 

Library: White Papers


 

Date and Time Manipulation on Teradata

Written by Jeff Ohlman


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

.





 
  Top Home Join Privacy Feedback  
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky
Copyright 2016 - All Rights Reserved
Last Modified: 01 Jan 2016