Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 09 Nov 2007 @ 16:33:08 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Extracting time from numeric field
From:   Geoffrey Rommel

SS, you need to give more details, particularly the format of the column you're trying to convert along with some examples. But let me try to address the problem with the information available.

     >  SELECT ...
     > -- '22:33:44' as arrmt_made_tm,
     > cast(substring((trim(drv.DT_TM_NUM))(char(25)) from 9 for 6)
     >          as time(6)) abc,
     >          drv.arr_made_dt ARR_MADE_DT,

Apparently DT_TM_NUM is a numeric column to be converted to time. You are converting it to char(25) with a default format, then converting that to time(6).

Using the default format is risky because in principle it could change at any time, and then your code would not give the same results. Unlikely, but it's better to play it safe. Also, time(6) means a time with 6 decimal places in the seconds. You seem to want just hh:mi:ss, so you need time(0).

Your input seems to be a decimal(14) in the form yyyymmddhhmiss. To extract the time portion, simply take the remainder after dividing the number by 1E6 and convert that to time(0), thus:

     *cast*(*cast*(*cast*(DT_TM_NUM MOD 1000000 *as* format '99:99:99') *as*
     char (8)) *as* time (0))

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023