Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decimal Hours Duration to HH:MM Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 6:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:55 PM
Points: 92, Visits: 265

Hi,

Anyone got a simple method for converting decimal time duration to HH:MM.

For example:

1.5 to 1:30
30.75 to 30:45
95.25 to 95:15

etc

Many thanks for any assistance.

Matthew
Post #1352146
Posted Thursday, August 30, 2012 7:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 7:49 AM
Points: 92, Visits: 104
Hey,

This will do it BUT - You arent getting days in that.

DECLARE @hours decimal(15,4)
SELECT @hours = 28.75
SELECT 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.html

HTH

Ta
Post #1352179
Posted Thursday, August 30, 2012 7:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079

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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 12,910, Visits: 32,026
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

--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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 7:49 AM
Points: 92, Visits: 104
Thats handy Lowell, nice one. All options there for you OP
Post #1352196
Posted Thursday, August 30, 2012 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:55 PM
Points: 92, Visits: 265
@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.75
select replace(cast(convert(decimal(10,2),cast(@time as int)+(((@time-cast(@time as int))*.60))) as varchar),'.',':')

Which seems to work nicely

Thanks a lot.

Post #1352202
Posted Thursday, August 30, 2012 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:55 PM
Points: 92, Visits: 265
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
m.dunster (8/30/2012)


@Laurie - Thanks for this tweaked it to:

declare @time decimal(5,2);
set @time = 95.75
select replace(cast(convert(decimal(10,2),cast(@time as int)+(((@time-cast(@time as int))*.60))) as varchar),'.',':')

Which seems to work nicely

Thanks 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse