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 2014 - All Rights Reserved
Last Modified: 03 Mar 2014
 

Recent Threads

Attachments

Library

White Papers

UDFs

 

Quick Reference

Rules of Conduct

FAQs

Join the Forum

 

Archives

Sample Index

2014  2006
2013  2005
2012  2004
2011  2003
2010  2002
2009  2001
2008  2000
2007  1999