SUBSTR
nazlfcJul 29 2011 — edited Aug 10 2011Hi,
I am currently faced with a little issue where I have values for time spent (duration) in a varchar2(14) field which are stored in the format of either hhhh:mm:ss.ssss or hh:mm:ss.ssss i.e. 0010:40:34.1232 or 01:40:23.2345. I would like to return data using only SQL and the format should be hh:mm:ss meaning trimming any instances of leading 0's and all the milliseconds. Therefore, when the times are like 0009:56:45.3456 then I would like this to be returned as 09:56:45.
I have got up to a certain point and could do with some advice.
I am currently using the case statement and the substr function to derive this. So currently this is the logic I am using:
CASE
WHEN SUBSTR(duration_column, 1,4) = '0000'
THEN SUBSTR(duration_column, 1,10)
ELSE substr(duration_column,1,8)
END
What I now would like to do is when there are instances where the times have hours with four digits is to trim the first 2 leading zeros to ensure we get times in the format of hh:mm:ss. The 2 leading zeros are not required but the system occasinally stores values in the database like this because database field it is stored in is a varchar2 field.
Any help, advice or guideance will be greatly appreciated.
Thanks