sql function to convert hh:mm to decimal hours

Mohammed Muntazim 21 Reputation points
2022-10-12T06:57:17.29+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,872 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2022-10-13T02:04:16.087+00:00

    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.


5 additional answers

Sort by: Most helpful
  1. Olaf Helper 41,021 Reputation points
    2022-10-12T07:11:52.323+00:00

    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.


  2. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2022-10-12T07:30:57.89+00:00

    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.


  3. Viorel 112.9K Reputation points
    2022-10-12T07:38:40.77+00:00

    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  
    

  4. Sharath 46 Reputation points
    2022-10-12T09:07:42.2+00:00

    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))

    0 comments No comments