Arithmetic Overflow

Question

Post reply

Arithmetic Overflow

TriggerMe

SSChampion

Points: 11752
More actions
May 26, 2009 at 9:23 pm

#134590

I have data in a column of Decimal datatype(38,20) like this,
0.00000000000000000000
10.00000000000000000000
10.00000000000000000000
10.00000000000000000000
0.00000000000000000000
I need to display this data as,
0.00
10.00
10.00
10.00
0.00
So i do this,
CAST(IL.[VAT %] AS DECIMAL(2,2)) AS 'VAT'
But i get the following error,
Arithmetic overflow error converting numeric to data type numeric.
How can I return the values as I have stated above?
Kindest Regards,

Viewing 15 posts - 1 through 15 (of 19 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this:

CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Ok thanks that works but why? Why can't you use 2,2?

Kindest Regards,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Trigger (5/26/2009)
Ok thanks that works but why? Why can't you use 2,2?

Because DECIMAL(2,2) represents a decimal number with 2 positions, both to the right of the decimal.

Please take some time to read about DECIMAL and NUMERIC data types in Books Online (BOL).

arun.sas SSChampion Points: 11831 More actions · Answer 4

Lynn Pettis (5/26/2009)
Try this:
CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'

hi Lynn,

CAST(ABC AS DECIMAL(4,2)) AS 'VAT'is better,

but the orignal datatype is Decimal(38,20),

if the value like 99999.00000000, then again the Arithmetic overflow error happned.

so,its may better to use this like

CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

ARUN SAS

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

arun.sas (5/26/2009)
Lynn Pettis (5/26/2009)
Try this:
CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'
hi Lynn,
CAST(ABC AS DECIMAL(4,2)) AS 'VAT'is better,
but the orignal datatype is Decimal(38,20),
if the value like 99999.00000000, then again the Arithmetic overflow error happned.
so,its may better to use this like
CAST(ABC AS DECIMAL(38,2)) AS 'VAT'
ARUN SAS

My response was based on the OP's original post that included the sample data. It is possible that the original definition of DECIMAL(38,20) was simply overkill for the data stored.

Paul White SSC Guru Points: 150467 More actions · Answer 6

arun.sas (5/26/2009)
Lynn Pettis (5/26/2009)
Try this:
CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'
but the orignal datatype is Decimal(38,20),
so,its may better to use this like
CAST(ABC AS DECIMAL(38,2)) AS 'VAT'

A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

arun.sas SSChampion Points: 11831 More actions · Answer 7

Paul White (5/26/2009)
arun.sas (5/26/2009)
Lynn Pettis (5/26/2009)
Try this:
CAST(IL.[VAT %] AS DECIMAL(4,2)) AS 'VAT'
but the orignal datatype is Decimal(38,20),
so,its may better to use this like
CAST(ABC AS DECIMAL(38,2)) AS 'VAT'
A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:

Hi,

Never assume the VAT means “Value Added Tax”.

Wait till OP’s response.

ARUN SAS

Paul White SSC Guru Points: 150467 More actions · Answer 8

arun.sas (5/26/2009)
Paul White (5/26/2009)
A country where the VAT rate requires a DEC(38,2) is likely to experience civil unrest sooner rather than later. :w00t:
Never assume the VAT means “Value Added Tax”. Wait till OP’s response.

Msg 50000, Level 25, State 1, Line 1

General failure in module sense_of_humour.cpl at line 1.

The connection has been terminated.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 9

I'm curious about what you mean with "display", Trigger?

😉

Flo

TriggerMe SSChampion Points: 11752 More actions · Answer 10

Old Hand is correct! It's not Value Added Tax.

Kindest Regards,

Paul White SSC Guru Points: 150467 More actions · Answer 11

Trigger (5/27/2009)
Old Hand is correct! It's not Value Added Tax.

Tough crowd :pinch:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

arun.sas SSChampion Points: 11831 More actions · Answer 12

Paul White (5/27/2009)
Trigger (5/27/2009)
Old Hand is correct! It's not Value Added Tax.
Tough crowd :pinch:

Real things comes late but latest.

Fine!!!

ARUN SAS

Paul White SSC Guru Points: 150467 More actions · Answer 13

arun.sas (5/27/2009)
Real things comes late but latest.
Fine!!!

You what? :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

Paul, we need to go have a pint, discuss the flag, and get your head unfuzzled. Your sense of humour is underappreciated here. 😛

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills