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.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-03-16 : 12:01:05
|
| I am doing an average of c1 as follows: avg(Cast(Table1.C1 as smallmoney)) which is returning n.nnnn value.I want only two digits after the decimal. am I missing any function that does it? Is there any method to do it without further conversions?column(c1) data type: smallintTIA |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-16 : 12:03:37
|
| Try using the numeric or decimal datatypes:SELECT AVG(CAST(Table1.C1 AS NUMERIC(10,2))) FROM ....OS |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-16 : 12:07:51
|
| Thanks for the reply, it did not work though. returns me n.nnnnnn |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-03-16 : 12:20:57
|
| Put the CAST xxxx AS NUMERIC outside everything...not inside. |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-16 : 12:36:16
|
| I did not quite follow. I am using CAST because the datatype is smallint and I want avg() for upto 2 decimal points. Am I missing something?Would you please give an example?TIA |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-16 : 12:45:09
|
| What he meant was this:SELECT CAST(AVG(CAST(Table1.C1 AS NUMERIC(10,2)))AS NUMERIC(10,2)) FROM ...But I always thought that the AVG function returned a value of the same datatype as the parameter. So I am not quite sure why this did not work as intended.OS |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-16 : 13:10:56
|
| Hmm.. lokks like conversion over conversion is inevitable.Thanks a lot for all help. It works... |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-17 : 03:22:11
|
| Just my $0.02 cents, but this looks more like a presentational issue and therefore is an easy exercise for your client app. Or are the results needed for further processing?--Frankhttp://www.insidesql.de |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-17 : 09:26:20
|
| Thanks for the input. You are right, it is a presentation issue. I am very limited with my front end. It is an 'out of the box' solution.Thanks again |
|
|
|
|
|
|
Datatype conversion? Simple question