Teradata Tips Info

Teradata has two types of time types, the old one that is simply an integer format ’99:99:99′ and the new ANSI TIME type. The integer-based one can be used for comparisons but not for time calculations. The new one can be used for everything. This is how you can convert to the new type if you have the old type. Assume you have a table called T with an integer-based column UPDATE_TM like:

CREATE TABLE T
(
   UPDATE_DT DATE FORMAT 'YYYY-MM-DD',
   UPDATE_TM INTEGER FORMAT '99:99:99' )
);

To convert the integer time UPDATE_TM to a TIME:

SELECT
  TIME '00:00:00'
  + CAST(update_tm / 10000 AS INTERVAL HOUR)
  + CAST((update_tm / 100) MOD 100 AS INTERVAL MINUTE)
  + CAST((update_tm MOD 100) AS INTERVAL SECOND)
FROM T

To convert the UPDATE_DT, UPDATE_TM to a timestamp(0):

SELECT 
  CAST(update_dt AS TIMESTAMP(0))
  + CAST(update_tm / 10000 AS INTERVAL HOUR)
  + CAST((update_tm / 100) MOD 100 AS INTERVAL MINUTE)
  + CAST((update_tm MOD 100) AS INTERVAL SECOND)
FROM T

Tags: TeradataTip

This entry was posted on Saturday, May 30th, 2009 at 18:22:29 and is filed under TeradataTipInfo. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.