Convert Values ( possible ) - SQL Server Forums

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

SQL Server Development (2000)

Convert Values ( possible )

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-06 : 14:06:15

I have a query that works perfectly except the values : Elapsedsecs are in seconds and I need to display them in my results as minutes rounded to first decimal place. Is it possible given this query or should I just take the resultset and fix it ?

SELECT Customer, SUM(Elapsedsecs) AS SystemTime, MIN(TimeStart) AS Started
FROM dbo.TestBlockStatus
WHERE (SystemName = '2711 ') AND (TimeEnd >= '04/12/2005') AND ('05/12/2005' >= TimeEnd)
GROUP BY Customer
ORDER BY SystemTime

Thanks !

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-06 : 14:33:14

I think I have I am finding my way here:
SELECT Customer, CONVERT(real, SUM(Elapsedsecs) / 60.0) AS SystemTime, MIN(TimeStart) AS Started
FROM dbo.TestBlockStatus
WHERE (SystemName = '2711 ') AND (TimeEnd >= '04/12/2005') AND ('05/12/2005' >= TimeEnd)
GROUP BY Customer
ORDER BY SystemTime

andrewcw

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-06 : 15:32:06

You should stay away from the REAL or FLOAT datatypes for most business purposes.

Try something like this:


SELECT
	Customer,
	convert(numeric(10,1),round(sum(elapsedsecs)/60.000 , 1)) AS SystemTime,
	MIN(TimeStart) AS Started
FROM
	dbo.TestBlockStatus
WHERE
	(SystemName = '2711 ')		AND
	(TimeEnd >= '04/12/2005')	AND
	('05/12/2005' >= TimeEnd)
GROUP BY
	Customer
ORDER BY
	SystemTime

CODO ERGO SUM

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-06 : 15:43:55

I had used ROUND(CONVERT(real, SUM(Elapsedsecs) / 60.0),1)
but your convert(numeric(10,1),SUM(Elapsedsecs)/60.0,1) seems to work equally well.
So can you tell me more. I am just a newbie.

andrewcw

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-06 : 16:44:15

What I am saying is to stay away from using the REAL and FLOAT datatypes for most business applications. They are more suitable for use in scientific applications.

Most business applications use some form of decimal arithmetic, while REAL and FLOAT represent values internally as binary numbers. There is not always an exact conversion from binary to decimal, so you get odd rounding errors. For example:


select num = 1.999999e

num                                                   
------------------- 
1.9999990000000001

(1 row(s) affected)

And good luck explaining to the accountants that it's nothing to worry about.

CODO ERGO SUM

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-06 : 17:27:46

OK Thanks for the tip. In my particular case we are talking seconds of execution time so the bean counter problem does not apply. However, if you had not advised me I could have made the mistake where it did matter. Probably a good habit to use your suggestion ! Again Thanks

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources