Archives of the TeradataForum

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

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

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>>