Converting hours into time notation

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)

Converting hours into time notation

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-06-21 : 11:38:28

How in SQL can I convert

8,079999 hours into 8:05 (8 hours and 5 minutes)

Henri

Henri
~~~~
The envious praises me unknowingly

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-21 : 11:52:42

You can use something along the lines of...

--input
declare @x decimal(18, 6)
set @x = 8.079999

--calculation
select left(convert(varchar, dateadd(minute, round(@x * 60, 0), 0), 108), 5)

/*results
08:05
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-06-21 : 12:02:18

You should get a reward for this! Brilliant!

Henri
~~~~
The envious praises me unknowingly

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-21 : 12:14:02

You can buy me a beer sometime...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-06-27 : 10:15:15

Hmm, the solution has a 24 hrs assumption.

I made a function for the minutes to hour notation.


ALTER FUNCTION [dbo].[fnMinutesToTime] 
(
	@Minutes AS int
)
RETURNS varchar(5)
AS
BEGIN
	DECLARE @Result varchar(5),
			@Hours int,
			@Reminder int

	SELECT @Hours = @Minutes / 60, @Reminder = @Minutes % 60
	SELECT @Result = CASE WHEN @Hours < 10 THEN '0' ELSE '' END + CAST(@Hours AS varchar(2)) + ':' +
					 CASE WHEN @Reminder < 10 THEN '0' ELSE '' END + CAST(@Reminder AS varchar(2))

	RETURN @Result

END

Henri
~~~~
The envious praises me unknowingly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources