Snowflake converting numeric value into HHMMSS format-CodePudding

I have a column that has number in it in the format of HHMMSS but the number is not consistent (not all have six digits), ex: "83455" "153651" "91251". The number 83455 would mean 8:34 am and 55 as SS. I tried converting into varchar and use TO_TIME but the output is not the same as it is. Similarly, I also tried converting into timestamp then get the time from it but it just won't work. The output I want here would be 8:34:55, what is the best way to convert the number?

CodePudding user response：

Try this. I split hours minutes and seconds and then concatenate them into time format.

   SELECT 
      CAST(FLOOR(col / 10000) || ':' || 
           FLOOR(MOD(col / 100, 100)) || ':' || 
           MOD(col, 100) AS TIME) AS converted_time
    FROM 
      yourtable

CodePudding user response：

An alternative approach is to use the built in function for this task. TRY_TO_TIME().

Sometimes the built in functions are easier to read, understand, less typing, optimized (runs faster/cheaper).