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: Sunday, May 3, 2015 8:09 AM 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
Post #1352146
 Posted Thursday, August 30, 2012 7:06 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 5, 2015 1:17 PM 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
Post #1352179
 Posted Thursday, August 30, 2012 7:13 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 `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:53 AM Points: 14,541, Visits: 38,382
 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!
Post #1352187
 Posted Thursday, August 30, 2012 7:18 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, November 5, 2015 1:17 PM Points: 93, Visits: 121
 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: Sunday, May 3, 2015 8:09 AM 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.
Post #1352202
 Posted Thursday, August 30, 2012 7:30 AM
 SSC Journeyman Group: General Forum Members Last Login: Sunday, May 3, 2015 8:09 AM Points: 92, Visits: 266
 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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 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