Changing Hours into Hours and Minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Changing Hours into Hours and Minutes

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-26 : 14:41:52

I need to change a decimal value representing hours into a string that represents hours and minutes.

For instance 1.5 should be converted into 1hr 30min. If the value is less than an hour it the hr part of the string should be left off. Ex: .25 = 15min.

I don't suppose their is a pre-defined function that does this is there?

Thanks-
Nick

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-26 : 14:53:01

I think this works pretty well.


DECLARE @MyDate DATETIME
DECLARE @MyDecial FLOAT

SELECT @MyDate = GETDATE()

SET @MyDecial = 1.5
--SET @MyDecial = .25
--SET @MyDecial = .5
SELECT DATEPART(hh, DATEADD(mi, @MyDecial * 60, @MyDate) - @MyDate) as Hours, DATEPART(mi, DATEADD(mi, @MyDecial * 60, @MyDate) - @MyDate) as Minutes

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-26 : 15:48:07

Thanks That Worked Great. I changed it a bit to do some of the formatting for me.


SELECT CASE
	WHEN DATEPART(hh, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE())=0 THEN 
	CAST(DATEPART(mi, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE()) AS varchar) + 'min'
	WHEN DATEPART(hh, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE())=1 THEN
	CAST(DATEPART(hh, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE()) AS varchar) + 'hr ' +  CAST(DATEPART(mi, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE()) AS varchar) + 'min'
	ELSE 
	CAST(DATEPART(hh, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE()) AS varchar) + 'hrs ' +  CAST(DATEPART(mi, DATEADD(mi, @MyDecial * 60, GETDATE()) - GETDATE()) AS varchar) + 'min'
	END
AS SegmentTime

The only problem I see is that it doesn't work if the number of hours is greater than 24, it rolls back after that, but in my case it doesn't matter. Thanks again.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-26 : 15:54:26

declare @d decimal(8,2)

set @d = 1.5

select case when @d >= 1 then convert(varchar(10),convert(int,@d)) + ' hr ' else '' end + convert(varchar(2),convert(int,60*(@d-convert(int, @d)))) + ' min'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/26/2003 19:22:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources