Post reply

Function for converting hh:mm:ss time to decimal equivalent

  • August 17, 2021 at 2:30 pm

    #3920800

    I have a user defined function that just needs a little tweaking. The first block of code used in a query returns 7 decimal places and when I wrap the code in a function it only returns 2 decimal places. Any clue would help... Thanks!

      ,CONVERT(DECIMAL(10,2),LEFT([LOGIN TIME],2) ) + 
       CONVERT(DECIMAL(10,2),SUBSTRING(CONVERT(VARCHAR(8),[LOGIN TIME]),4,2))/60 + 
       CONVERT(DECIMAL(10,2),RIGHT([LOGIN TIME],2))/3600 AS [Time]
    ----------------------------------------------------------------------------------------------
    ALTER FUNCTION [dbo].[fnGetDecimalTime]
    (
    @Time_HHMMSS varchar(10)
    )
    RETURNS decimal(10, 2)
    AS
    BEGIN
      RETURN
      (SELECT  CONVERT(DECIMAL(10,2),LEFT(@Time_HHMMSS,2) ) + 
       CONVERT(DECIMAL(10,2),SUBSTRING(CONVERT(VARCHAR(8),@Time_HHMMSS),4,2))/60 + 
       CONVERT(DECIMAL(10,2),RIGHT(@Time_HHMMSS,2))/3600 AS 'TimeDecimal')
    END

  • August 17, 2021 at 3:04 pm

    #3920819

    First of all, the function you wrote is converting the final output to DECIMAL(10,2) because that's exactly what you told it to do in the RETURN clause of the function.

    Second, you don't need to make things so bloody complicated.   For example, here's a single variable test of what I'm talking about.

    DECLARE @Time_HHMMSS VARCHAR(10) = '14:35:09'; --Setup for single value test.
     SELECT CONVERT(FLOAT,CONVERT(DATETIME,@Time_HHMMSS))*24; --Super simple solution

    Third, don't use a scalar function unless you absolutely have to with the understanding that 99.9% of the time, you don't have to.  Please read the following article on that subject and an alternative.

    https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply