Decimal Hours Duration to HH:MM

Question

Post reply

Decimal Hours Duration to HH:MM

Matthew24

SSCommitted

Points: 1736
More actions
August 30, 2012 at 6:26 am

#261197

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

cporteous SSC Eights! Points: 996 More actions · Answer 1

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

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

declare @time decimal(5,2);

set @time = 95.25;

select cast(@time as int)+(((@time-cast(@time as int))*.60));

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

cporteous SSC Eights! Points: 996 More actions · Answer 4

Thats handy Lowell, nice one. All options there for you OP

Matthew24 SSCommitted Points: 1736 More actions · Answer 5

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

Matthew24 SSCommitted Points: 1736 More actions · Answer 6

Thanks to you too Lowell for your input. Squirelled away for later use I'm sure !

Matthew

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 7

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),'.',':')