Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Decimal Hours Duration to HH:MM Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 30, 2012 6:26 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, November 06, 2013 9:19 AM Points: 91, Visits: 264
 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
Post #1352146
 Posted Thursday, August 30, 2012 7:06 AM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, June 25, 2013 7:07 AM Points: 92, Visits: 101
 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
Post #1352179
 Posted Thursday, August 30, 2012 7:13 AM
 Old Hand Group: General Forum Members Last Login: Today @ 4:30 AM Points: 307, Visits: 950
 `declare @time decimal(5,2);set @time = 95.25;select cast(@time as int)+(((@time-cast(@time as int))*.60));`
Post #1352184
 Posted Thursday, August 30, 2012 7:15 AM
 SSChampion Group: General Forum Members Last Login: Today @ 6:25 AM Points: 12,283, Visits: 29,470
 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--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352187
 Posted Thursday, August 30, 2012 7:18 AM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, June 25, 2013 7:07 AM Points: 92, Visits: 101
 Thats handy Lowell, nice one. All options there for you OP
Post #1352196
 Posted Thursday, August 30, 2012 7:21 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, November 06, 2013 9:19 AM Points: 91, Visits: 264
 @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.
Post #1352202
 Posted Thursday, August 30, 2012 7:30 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, November 06, 2013 9:19 AM Points: 91, Visits: 264
 Thanks to you too Lowell for your input. Squirelled away for later use I'm sure !Matthew
Post #1352211
 Posted Thursday, August 30, 2012 7:36 AM
 Old Hand Group: General Forum Members Last Login: Today @ 4:30 AM Points: 307, Visits: 950
 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),'.',':')`
Post #1352217

 Permissions