Teradata - Function dates demo

select

       -- Date pieces

       current_date as Today

       , current_timestamp as aTimestamp

       , current_date (format 'yyyyddd') (char(7)) as Formatyyyydd

       , extract( year from date '2012-06-06' ) as TheYear

       , extract( month from date '2012-06-06' ) as TheMonth

       , extract( day from date '2012-06-06' ) as TheDay

       -- Period ID

       , cast(

              ( extract( year from current_date ) * 100 )

              + extract( month from current_date )

              as int

       ) as PeriodID

      

              -- Alternative

              , CAST( cast((current_date (format 'YYYYMM')) as varchar(6)) AS INT ) as PeriodIDAlt

             

       -- Day ID

       , cast(

              ( extract( year from current_date ) * 10000 )

              + ( extract( month from current_date ) * 100 )

              + extract( day from current_date )

              as int

       ) as DayID

      

       -- Timestamp conversions

       , cast(

              cast(CURRENT_DATE as timestamp(3))

              + ((CURRENT_TIME - time '00:00:00') hour to second(0))

              as timestamp(3)

       ) as Timestamp3

       , current_date (format 'yyyyddd') (char(7)) as JulianDate

       -- Date artithmetic

       , current_date + 1 as Tomorrow

       , add_months( current_date, 1) as NextMonth

              --, current_date + interval '1' month as NextMonth2

       , add_months( current_date, -1) as PreviousMonth

              --, current_date - interval '1' month as PreviousMonth2

       , current_date - date '2012-01-31' as DateDiffInDays

       , cast(

              cast(

                     cast( current_date - date '2012-01-31' as float)

                     / cast(30.5 as float)

                     as decimal(4,0)

              )

              as byteint

       ) as DateDiffInMonths

;