## Decimal Hours Duration to HH:MM

 Author Message m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Hi,Anyone got a simple method for converting decimal time duration to HH:MM.For example:1.5 to 1:3030.75 to 30:4595.25 to 95:15etcMany thanks for any assistance.Matthew cnporteous SSC Journeyman Group: General Forum Members Points: 93 Visits: 121 Hey,This will do it BUT - You arent getting days in that. `DECLARE @hours decimal(15,4)SELECT @hours = 28.75SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@hours)),2)   +':'   + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hours-FLOOR(@hours))*60))),2)   +':'   + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hours-FLOOR(@hours))*60)-FLOOR(((@hours-FLOOR(@hours))*60)))*60),2)   `Here's the thread from google. There's another method there too which will get you the full datetime data type too.http://www.dbforums.com/microsoft-sql-server/1202305-converting-decimal-time.htmlHTHTa laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 `declare @time decimal(5,2);set @time = 95.25;select cast(@time as int)+(((@time-cast(@time as int))*.60));` Lowell SSChampion Group: General Forum Members Points: 14887 Visits: 38887 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 ALLSELECT 30.75 UNION ALLSELECT 40.00 UNION ALLSELECT 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 MinutesPortionFROM 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! cnporteous SSC Journeyman Group: General Forum Members Points: 93 Visits: 121 Thats handy Lowell, nice one. All options there for you OP m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 @Cnporteus, yes saw that one, didn't work for high enough hours though.@Laurie - Thanks for this tweaked it to:`declare @time decimal(5,2);set @time = 95.75select replace(cast(convert(decimal(10,2),cast(@time as int)+(((@time-cast(@time as int))*.60))) as varchar),'.',':')`Which seems to work nicelyThanks a lot. m.dunster SSC Journeyman Group: General Forum Members Points: 92 Visits: 266 Thanks to you too Lowell for your input. Squirelled away for later use I'm sure !Matthew laurie-789651 SSC-Addicted Group: General Forum Members Points: 424 Visits: 1271 m.dunster (8/30/2012)@Laurie - Thanks for this tweaked it to:`declare @time decimal(5,2);set @time = 95.75select replace(cast(convert(decimal(10,2),cast(@time as int)+(((@time-cast(@time as int))*.60))) as varchar),'.',':')`Which seems to work nicelyThanks a lot.Even better!BTW - I left a surplus pair of brackets in - removed below:`select replace(cast(convert(decimal(10,2),cast(@time as int)+((@time-cast(@time as int))*.60)) as varchar),'.',':')`