Strange Results Converting To Hours

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

General SQL Server Forums

New to SQL Server Programming

Strange Results Converting To Hours

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2008-03-06 : 05:38:12

Hi
I'm getting strange results when converting a number to hours and minutes.

The following section of code....

AbsentMinutes,
CONVERT(varchar(5), DATEADD([minute], [AbsentMinutes], 0), 108) AS AbsentHours,
[Term 1 Minutes], 
CONVERT(varchar(5), DATEADD([minute], [Term 1 Minutes], 0), 108) AS Term1Hours,

......is yielding the following results.

AbsentMins 6480 AbsentHours 12:00
Term 1 Mins 21540 Term 1 Hours 23:00

Obviously thats not right but I cant see where.

Can anyone advise please? This has worked for me previously on other data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 06:06:47

They are correct, since there are only 1440 minutes per day...

DECLARE	@Sample TABLE
	(
		Mins INT
	)

INSERT	@Sample
SELECT	5 UNION ALL
SELECT	1502 UNION ALL
SELECT	6480 UNION ALL
SELECT	21540

SELECT	Mins,
	DATEADD(MINUTE, Mins, '19000101 00:00:00.000') AS ChipEmbele,
	CASE
		WHEN Mins >= 2880 THEN CAST(Mins / 1440 AS VARCHAR(12)) + ' days, '
		WHEN Mins >= 1440 THEN '1 day, '
		ELSE ''
	END +
	CASE
		WHEN Mins % 1440 >= 120 THEN CAST((Mins % 1440) / 60 AS VARCHAR(12)) + ' hours and '
		WHEN Mins % 1440 >= 60 THEN '1 hour and '
		ELSE ''
	END + 
	CASE
		WHEN Mins % 60 >= 2 THEN CAST(Mins % 60 AS VARCHAR(12)) + ' minutes '
		WHEN Mins % 60 >= 1 THEN '1 minute'
		ELSE '0 minutes'
	END AS Peso
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

chipembele
Posting Yak Master

106 Posts

Posted - 2008-03-06 : 06:30:16

Thanks. Doesnt look like theres an easy way to do this without rewriting my code. The bit I posted was just a sample. The whole script is about 200 lines long.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 08:42:31

Yes. You can rewrite the conversion for minutes to any format with a inline scalar user defined function.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 08:56:38

What are you trying to do here? Can you step back and explain the data you have and the results you are looking for? (keep it simple, but cover all the bases and possibilities) It Look to me like you are greatly over-complicating something simple that is probably easily done if you use proper data types and simply let your client handle hh:mm formatting.

This article may be helpful to you, but I am not 100% sure what you are doing so it may not apply:

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

- Jeff
http://weblogs.sqlteam.com/JeffS

chipembele
Posting Yak Master

106 Posts

Posted - 2008-03-06 : 09:07:38

Hi
I've decided to simply ignore minutes for this purpose and just format to zero decimal place to get total hours.

Thanks for your help, I will bear it in mind if I have a task that forces me to calculate minutes in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources