I have included all the possibility that hours could come in our production DB. The result column is generated from an hour column, where every time is converted into hour format. My code is not working for the 4th case(I marked with red). Any help with how I can fix this. Till Now, I have tried this sql thing.
SELECT hours,result=(CASE
WHEN hours LIKE '%h%' and hours NOT LIKE '%m%'
THEN SUBSTRING(hours,1,CHARINDEX('h', hours)-1) + Right(SUBSTRING(hours,CHARINDEX('h', hours)+1,2),2)
WHEN hours LIKE '%mn%'
THEN CAST(SUBSTRING(hours,1,CHARINDEX('m', hours)-1) as decimal(10,3))/60
ELSE 'FAIL'
END)
FROM aaa_Test_hours
hours | result |
---|---|
1.5h | 1.500000 |
4h | 4.000000 |
30mn | 0.500000 |
1h30 | 130.000000 |
11h | 11.000000 |
6H | 6.000000 |