Converting a Timestamp into a date

When loading a DB2 timestamp into a Teradata table, BO considers this Teradata Timestamp as a character field. I have tried to convert a Timestamp into a truly date field so that I can do ‘days between’ calculations, but have had no luck. Any suggestions?


Ted Michalski :us: (BOB member since 2002-08-20)

I’m not really familiar with the format of a DB2 Timestamp, but I think you will have to do the math in Teradata. The following is from a whitepaper by Geoffrey Rommel on the teradata forum:

sel cast(ts01 as date) - cast(ts04 as date)
   from dttest;

(ts01-ts04) 
----------- 
        226 -- type of this result is INTEGER 

sel ts01 - ts04 day(4)
   from dttest;

(ts01 - ts04) DAY 
----------------- 
              226 -- type of this result is INTERVAL DAY 

You can also use cast to store the timestamp in another column as a true date field, and do additional math in BO.


charlie :us: (BOB member since 2002-08-20)

OK, I was able to ‘extract’ the dat from a timestamp using the CAST(XXX as DATE) function. How do I extract the time?


Ted Michalski :us: (BOB member since 2002-08-20)

Try something like

select cast(TTT as time(0)) from mytable;

charlie :us: (BOB member since 2002-08-20)