batman32
asked on
SQL2000 Round Function
Hi All,
Can you please help on the following function:
SELECT dbo.Duration * dbo.Price AS TOTAL_PRICE
The current result rounds of with no decimal places. How can we have TOTAL_PRICE with 5 decimal places even if it shows for example 5.00000
Thanks!
Can you please help on the following function:
SELECT dbo.Duration * dbo.Price AS TOTAL_PRICE
The current result rounds of with no decimal places. How can we have TOTAL_PRICE with 5 decimal places even if it shows for example 5.00000
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT dbo.Duration * , Cast(Round(dbo.Price,0) As Decimal(7,6)) AS TOTAL_PRICE
ASKER
dbo.Price,0 made Total Price 0
can you post what query you wrote..
You have to write the ROUND Function.. before that .. have a look at my query again..
Select Cast (Round(5.7980,0) as decimal(7,6))
You have to write the ROUND Function.. before that .. have a look at my query again..
Select Cast (Round(5.7980,0) as decimal(7,6))
ASKER
HI,
SELECT dbo.Duration * Cast(dbo.Price As Decimal(7,6)) AS TOTAL_PRICE
is working, please tell us how to change the curency from US $ to anything else. All our fields are numeric....
Thanks!
you mean to say you are getting the output with $ sign?? can you post what kind of output u r getting
ASKER
USD (dollar sign) is our otput, but we need it just numeric
SELECT Cast(dbo.Duration * Cast(dbo.Price As Decimal(7,6)) As numeric (6,7) AS TOTAL_PRICE
??
??
ASKER
Incorrect syntax near '6'
opps typo
SELECT Cast(dbo.Duration * Cast(dbo.Price As Decimal(7,6)) As numeric (7,6)) AS TOTAL_PRICE
SELECT Cast(dbo.Duration * Cast(dbo.Price As Decimal(7,6)) As numeric (7,6)) AS TOTAL_PRICE
ASKER
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Dude, we are completely lost...
The statement has been terminated.
Dude, we are completely lost...
Are you using Query Analyser for running this..
Then do the following setting
1) Go to the Tools --> Options
2) In the Options go to the Connection Property.
3) There uncheck the option Use Regional Setting....
4) Then run this query..
SELECT dbo.Duration * Cast(dbo.Price As Decimal(7,6)) AS TOTAL_PRICE
Hope fully it should work fine..
Then do the following setting
1) Go to the Tools --> Options
2) In the Options go to the Connection Property.
3) There uncheck the option Use Regional Setting....
4) Then run this query..
SELECT dbo.Duration * Cast(dbo.Price As Decimal(7,6)) AS TOTAL_PRICE
Hope fully it should work fine..
ASKER
It works, but why we getting the last result as currency? ex $ 5.7980