Home Forums SQL Server 2008 SQL Server Newbies Decimal Hours Duration to HH:MM RE: Decimal Hours Duration to HH:MM

  • August 30, 2012 at 7:15 am

    #1531862

    Here's my first guess at it; i left lots of stuff in there to look at to help understand it.

    With PayRollHours (TheTime)

    As

    (

    SELECT 1.5 UNION ALL

    SELECT 30.75 UNION ALL

    SELECT 40.00 UNION ALL

    SELECT 95.25

    )

    SELECT DATEADD(minute,TotalMinutesInteger,'1900-01-01') ResultsAsDateTime,

    CONVERT(VARCHAR(5),DATEADD(minute,TotalMinutesInteger,'1900-01-01'),108) ResultAsString,

    *

    FROM (

    SELECT TheTime * 60 AS TotalMinutes,

    CONVERT(int,TheTime * 60 ) As TotalMinutesInteger,

    CONVERT(int,TheTime * 60 ) / 60 As HoursPortion,

    CONVERT(int,TheTime * 60 ) %60 As MinutesPortion

    FROM PayRollHours

    ) X

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!