0

I need help to convert string value to timestamp(6) based on case when condition as source values are not always in same format, mainly 2 formats available as mentioned in below examples:

START_FNMOMENT_STR is varchar(100) and START_FNMOMENT is TIMESTAMP(6) column

     Input:

   START_FNMOMENT_STR          
  4/17/2023 10:57:54         
  4/16/2023 14:10:13         
  4/16/2023                 
  4/12/2023                  
  4/16/2023 09:45:00    

  Expected Output:
  START_FNMOMENT
   4/17/2023 10:57:54.000000
   4/16/2023 14:10:13.000000
   4/16/2023 00:00:00.000000
   4/12/2023 00:00:00.000000
   4/16/2023 09:45:00.000000

I was trying this:

SEL START_FNMOMENT_STR
    ,CAST(START_FNMOMENT_STR AS TIMESTAMP(0),'YYYY-MM-DD hh24missff6') AS START_FNMOMENT 
FROM Table_A ;
nbk
  • 45,398
  • 8
  • 30
  • 47
Debasis
  • 21
  • 3
  • The format must match your input. And Teradata requires double digit numbers, '04' instead of '4'. This might work: `to_timestamp(rpad(regexp_replace(trim(START_FNMOMENT_STR), '\b(\d)\b', '0\1'), 19, ' 00:00:00'), 'mm/dd/yyyy hh24:mi:ss')` – dnoeth Apr 23 '23 at 19:15

0 Answers0