How to use Cast & Convert

Question

How to use Cast & Convert

asita

SSCarpal Tunnel

Points: 4144
More actions
April 17, 2018 at 6:06 pm

#330394

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
OR
CAST (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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 1

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.

create table bankstatement
(
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

sgmunson SSC Guru Points: 110639 More actions · Answer 2

asita - Tuesday, April 17, 2018 6:06 PM
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
OR
CAST (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

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)

ScottPletcher SSC Guru Points: 100951 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

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.