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