none
Convert Time in Hours and Minutes to Decimal using T-SQL

    Question

  • I am trying to convert hours and minutes to decimal and arrive at a sum of time taken.
    can any one provide me with the code.
     The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

    SELECT DATENAME(weekday, Started) AS Day,   
        C.Category,
        ClientCode,Description,
        dbo.FormatDateTime(Started, 'HH:MMTongue TiedS 12') as Started,
        dbo.FormatDateTime(Ended, 'HH:MMTongue TiedS 12') as Ended,
    CONVERT(varchar,TimeTaken,108) AS TotalTimeSpent
    FROM dbo.Journal J
    LEFT OUTER JOIN dbo.Categories C ON
    J.CategoryID = C.CategoryID
    WHERE
    --DATENAME(weekday, Started) =@Weekday
     AND
    Started >= @StartDate
    ORDER BY Day, Category, Started
    Wednesday, July 30, 2008 3:43 PM

Answers

  • You might find this helpful...

     

    Code Snippet

    declare @hms varchar(8)
    set @hms = '10:30:00'
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)

     

    select @minutes/60+@hours

     

     

     

    Wednesday, July 30, 2008 4:13 PM
  •  

    If you are just using Hours Minutes and Seconds...

    Value = Hours +( (Minutes + Seconds/60)/60)

    If you are just using Hours and Minutes
    Value = Hours + (Minutes/60)

    Wednesday, July 30, 2008 4:18 PM

All replies

  • You might find this helpful...

     

    Code Snippet

    declare @hms varchar(8)
    set @hms = '10:30:00'
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)

     

    select @minutes/60+@hours

     

     

     

    Wednesday, July 30, 2008 4:13 PM
  •  

    If you are just using Hours Minutes and Seconds...

    Value = Hours +( (Minutes + Seconds/60)/60)

    If you are just using Hours and Minutes
    Value = Hours + (Minutes/60)

    Wednesday, July 30, 2008 4:18 PM
  • Dear Jon,

    I am new to SQL.

    I have implemented the same as you said but I am getting an error.

    Can you please help me.

    My code.

    ALTER FUNCTION GETDECOT(@STROTHRS NVARCHAR(8)) RETURNS float AS BEGIN DECLARE @RESULT float declare @hms nvarchar(8) set @hms = @STROTHRS declare @hours int declare @minutes decimal set @hours = datepart(hour, @hms) set @minutes = datepart(minute, @hms) select @RESULT = convert(float,( @minutes/60+@hours)) RETURN @minutes/60+@hours END

    Or the other way...

    ================================================
    ALTER FUNCTION GETDECOT(@STROTHRS NVARCHAR(8))
    RETURNS float
    AS
    BEGIN
    DECLARE @RESULT float
    declare @hms nvarchar(8)
    set @hms = @STROTHRS
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)
    select @RESULT = convert(float,( @minutes/60+@hours))
    
    RETURN @RESULT
    END


    QUERY & Error: 

    select dbo.GETDECOT(STRINGHOURS) from TIMETBL
    
    ERROR:
    Conversion failed when converting date and/or time from character string.
    
    


    VSVAS


    • Edited by VSVASARMA Saturday, October 17, 2015 12:44 PM
    Saturday, October 17, 2015 12:42 PM
  • I tested the UDF-s designed by you. Both works.

    CREATE FUNCTION GETDECOT(@STROTHRS NVARCHAR(8))
    RETURNS float
    AS
    BEGIN
    DECLARE @RESULT float
    declare @hms nvarchar(8)
    set @hms = @STROTHRS
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)
    select @RESULT = convert(float,( @minutes/60+@hours))
    
    RETURN @minutes/60+@hours
    END
    GO
    
    CREATE FUNCTION GETDECOT1(@STROTHRS NVARCHAR(8))
    RETURNS float
    AS
    BEGIN
    DECLARE @RESULT float
    declare @hms nvarchar(8)
    set @hms = @STROTHRS
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)
    select @RESULT = convert(float,( @minutes/60+@hours))
    
    RETURN @RESULT
    END
    GO
    
    declare @hms varchar(8);
    set @hms = '10:30:00';
    SELECT dbo.GETDECOT( @hms);
    GO
    -- 10.5
    
    declare @hms varchar(8);
    set @hms = '14:45:00';
    SELECT dbo.GETDECOT1( @hms);
    GO
    -- 14.75
    
    
    Datetime functions: http://www.sqlusa.com/bestpractices/datetimeconversion/





    Kalman Toth Database & OLAP Architect Artificial Intelligence
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Saturday, October 17, 2015 2:24 PM
    Moderator
  • isnt this enough?

    SELECT DATENAME(weekday, Started) AS Day,    
        C.Category, 
        ClientCode,Description,
        DATEDIFF(mi,0,Started)/60.0 AS Started,
        DATEDIFF(mi,0,Ended)/60.0 AS Ended, 
    CONVERT(varchar,TimeTaken,108) AS TotalTimeSpent
    FROM dbo.Journal J
    LEFT OUTER JOIN dbo.Categories C ON 
    J.CategoryID = C.CategoryID
    WHERE
    --DATENAME(weekday, Started) =@Weekday 
     AND 
    Started >= @StartDate 
    ORDER BY Day, Category, Started


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, October 17, 2015 4:41 PM
  • This mostly works, but it's rounding to whole numbers for me. I needed more precision.

    If you need precision for decimal places, add the number of decimal places to the denominator. i.e. divide by 60.00 instead.

    Not sure why SQL Service requires that, but that's what I've found w/ the version I'm using.

    Quick test in SSMS:

    SELECT 30/60

    Result: 0

    SELECT 30/60.00

    Result: 0.50

    Tuesday, June 13, 2017 11:18 PM