How to use Cast & Convert
-
Hello Good Evening
SUM(Principal1) + SUM(BankInterest) + SUM(TAltrate) + SUM(PanaltyCharge) AS
Amount
I need to convert this to Decimal (7,2)
CAST (SUM
(Principal1) AS DECIMAL(5,2) ) + CAST ( SUM(BankInterest) AS DECIMAL(5,2)) + CAST(SUM(TAltrate) as DECIMAL(5,2) AS
Amount
ORCAST (SUM
(Principal1) + SUM(BankInterest) + SUM(TAltrate) + SUM(PanaltyCharge) AS DECIMAL(5,2))
Which one is best please advise also can i use Cast or Convert which is best suggested one? please advise
It would be great if you can say why one is better over other
Thanks in advance
Asita -
I don't know whether cast is better or convert is better.
CAST (SUM(Principal1) AS DECIMAL(5,2) ) + CAST ( SUM(BankInterest) AS DECIMAL(5,2)) + CAST(SUM(TAltrate) as DECIMAL(5,2) AS Amount
or
CAST (SUM(Principal1) + SUM(BankInterest) + SUM(TAltrate) + SUM(PanaltyCharge) AS DECIMAL(5,2))
Above query gives almost similar results/value most of the time .But sometimes it may be produce different results as well which I am explaining using below query.
(
principal float,
atmcharges float
);insert into bankstatement values (550.4545,20.1233333);
select
CAST(principal as decimal(5,2)),
CAST(interest as decimal(5,2)),
CAST(atmcharges as decimal(5,2)),
totalamtavailable=(CAST(principal as decimal(5,2))+
CAST(interest as decimal(5,2))+CAST(atmcharges as decimal(5,2))) ,
totalamt=CAST(principal+interest+atmcharges AS decimal(5,2))
from
(select principal,interest=(principal*4.5)/100,atmcharges
from bankstatement)b
Saravanan
- asita - Tuesday, April 17, 2018 6:06 PM
That usually depends on the size of your numeric data. However, there's no reason to use CAST over CONVERT, because under the covers it's going to CONVERT anyway, so unless you've already got coding standards in place to use CAST over CONVERT, it's not going to make much difference which one you use. The more important thing is how many times you use it. If you have to add a number of float or real data types together, you may have difficulty because float and real data types can NOT reproduce all possible values in the range for their data type, so you almost always lose accuracy with adding them together. When you convert might have to be tested, but do understand that CONVERT should be to a data type large enough to hold the result of whatever is being converted. When you do that with SUMS, you can always use it just once on the SUMS having been added together. Again, you may need to convert any values that are float to the right type BEFORE you SUM them or risk a loss of accuracy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Use CAST (it's ANSI-standard), and CAST the final result as well, if you want a specific size of result:
CAST( CAST( SUM(Principal1) AS DECIMAL(5,2) ) + CAST ( SUM(BankInterest) AS DECIMAL(5,2) ) +
CAST( SUM(TAltrate) as DECIMAL(5,2) ) AS DECIMAL(7, 2) )SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
What is the data types of the original columns: Principal1, BankInterest, TAltrate, PanaltyCharge ?
I think that will help you determine when to do the CAST, for each column or just for the result.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply