Converting "3H 30m" into 3.5-CodePudding

I am creating a SQL report for someone in which exists a column called Labour Hours of Engineer. In the column, the answers are shown as "3H 30M".

I want to change it to decimal. For example, "3H 30M" becomes 3.5. Or another example is if an engineer works for 23 minutes, in the column, the answer should be 0.38 (rounded to 2 decimal places). 0.38 is the answer when you divide 23 mins by 60.

My current Formula is:

CONVERT(VARCHAR(40), CAST(Labour AS INT)%(24*60)/60)   '.'   CONVERT(VARCHAR(40),  CAST(Labour AS INT)`)

Any way to achieve this, please share.

CodePudding user response：

Assuming that the 'H' and 'M' are always there, one method would be some use of CHARINDEX to find the 'H' and return the "first" characters and also strip those first characters. For minutes, you can just use simple division to get the decimal value

DECLARE @Time varchar(7) = '3H 30M';

SELECT @Time,
       CONVERT(int,LEFT(@Time,CHARINDEX('H',@Time)-1))   (CONVERT(decimal(2,0),REPLACE(STUFF(@Time,1,CHARINDEX('H',@Time),''),'M',''))/60);

CodePudding user response：

This code will cover Hour minute or only minute

select Convert(decimal(26,2),
IIF(Labour like '%H%',(Convert(int, substring(Labour,0,(CHARINDEX('H',Labour)))) * 60 ) IIF(Labour like '%M%',Convert(int, substring(Labour,CHARINDEX('H',Labour) 1,CHARINDEX('M',Labour)-CHARINDEX('H',Labour)-1)),0)
,IIF(Labour like '%M%',Convert(int, substring(Labour,0,CHARINDEX('M',Labour))),0)) /60.00)