sql function to convert hh:mm to decimal hours

Question

ALTER FUNCTION [dbo].[ConvertHHMMToDecimal]
(
@P_Value NVARCHAR(50)
)
RETURNS NVARCHAR(50)
BEGIN
DECLARE @V_Results NVARCHAR(50)
IF CHARINDEX(':', @P_Value) > 0
BEGIN
SET @V_Results='select cast(datediff(mi,CONVERT(datetime,'00:00:00',114"),CONVERT(datetime,@P_Value,114)) as decimal(12,2))/cast(60 as decimal(12,2))';
RETURN @V_Results
END
RETURN @P_Value

END
GO

If data is having ':' then taking converting minutes to hours.
Example:
1)Input file is having cumulative Hours as 12:6 then the value as 12+(6\60.0)=12+.1 =12.1 If it is decimal then rounding it to 2 digits if it is invalid or blank then preserving the same input value

Answer 1

Hi @MohammedMuntazim-1386

the output i'm getting is 102.30 but expected output is 102+(30/60.0)=102.5

Don't you find there is an IF clause IF CHARINDEX(':', @P_Value) > 0 in your function?
This means that when the data you apply does not contain ':' (say '120.30'), the original data will be returned.
If you want this function to work for both 'HH:MM' and 'HH. MM', you can use PATINDEX, instead of CHARINDEX, like this:

ALTER FUNCTION [dbo].[ConvertHHMMToDecimal]  
(  
@P_Value NVARCHAR(50)  
)  
RETURNS NVARCHAR(50)  
BEGIN  
DECLARE @V_Results NVARCHAR(50)  
IF PATINDEX('%[:.]%', @P_Value) > 0  
BEGIN  
SET @V_Results=(SELECT CAST(LEFT(@P_Value,PATINDEX('%[:.]%',@P_Value)-1) AS INT)+CAST(RIGHT(@P_Value,PATINDEX('%[:.]%',REVERSE(@P_Value))-1)AS INT)/60.0);  
RETURN @V_Results  
END  
RETURN @P_Value  
  
END  
GO  
  
select [dbo].[ConvertHHMMToDecimal] ('102.30')  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I'm not getting expected output

No surprise, the function returns the unmodified input parameter p_value.

Please post some sample input value and the expected result.

Answer 3

Hi @MohammedMuntazim-1386
If you are just using Hours : Minutes : Seconds
Then @V_Results= Hours +( (Minutes + Seconds/60)/60)

Check this sample:

DECLARE	@time	datetime = '03:46:12'  
SELECT	@time, CAST(@time AS float),  
		CAST(DATEPART(hh, @time) AS float) +  
		CAST(DATEPART(mi, @time) AS float) / 60 +  
		CAST(DATEPART(ss, @time) AS float) / 3600  

If you are just using Hours : Minutes
Then @V_Results= Hours + (Minutes/60)
Check this sample:

DECLARE @time varchar(10)  
SET @time='02:30'  
SELECT FLOOR(LEFT(@time,2))+CAST(RIGHT(@time,2)AS INT)/60.0  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

If your string also contains date (optionally), then check this function too:

ALTER FUNCTION [dbo].[ConvertHHMMToDecimal]  
(  
    @P_Value NVARCHAR(50)  
)  
RETURNS NVARCHAR(50)  
BEGIN  
  
    declare @dt datetime = try_cast(@P_value as datetime)  
    if @P_Value is null or @P_Value='' or @dt is null return @P_value  
  
    declare @V_Results nvarchar(50)  
  
    set @V_Results = format(datepart(hour, @dt) + datepart(minute, @dt) / 60.00, 'F2')  
  
    return @V_Results  
END  
GO  

Answer 5

Try this query:
Select cast( datediff(mi,CONVERT(time,'00:00:00',114),CONVERT(time,'12:06:21.55333',114)) as decimal(12,2))/cast(60 as decimal(12,2))