Arithmetic overflow error converting numeric to data type numeric

Question

I keep getting this error message everytime I run this query   Msg 8115  Level 16  State 8  Line 33 Arithmetic overflow error converting numeric to data type numeric  The statement has been terminated    But if I change the create table to  7 0   I don t get the error message But I need my data to be displayed as a decimal  I have tried 8 3 does not work   Is there any one who can help me work this Any help will be greatly appreciated   DECLARE  StartDate AS DATETIME DECLARE  StartDate y AS DATETIME DECLARE  EndDate AS DATETIME DECLARE  temp y AS DATETIME  SET  temp y   Dateadd yy  Datediff yy  0  Getdate     0  SET  StartDate y   Dateadd dd  1 - Datepart dw  Dateadd  ww   -2   temp y                                          Dateadd  ww   -2   temp y   SET  StartDate   Dateadd dd  1 - Datepart dw  Dateadd  ww   -2  Getdate                                        Dateadd  ww   -2  Getdate     SET  EndDate   Dateadd dd  6   StartDate   --temp table to hold all cities in list CREATE TABLE   temp          city VARCHAR 50       INSERT INTO   temp VALUES       ABERDEEN                  CHESAPEAKE                  Preffered-Seafood CHICAGO                  Preffered-Redist CHICAGO                  CLACKAMAS                  COLUMBUS                  CONKLIN                  DENVER                  FORT WORTH                  HANOVER PARK                  JACKSONVILLE                  LAKELAND                  MONTGOMERY                  PFW-NORTHEAST                  PFW-SOUTHEAST                  RIVERSIDE                  TRENTON CANADA                  VERNON    --temp to hold data for the cities CREATE TABLE  temp          city            VARCHAR 50        ytdshipments    INT       ytdtotalweight  DECIMAL 7  2  NOT NULL       ytdtotalcharges DECIMAL  7  2  NOT NULL   --YTDRevperPound decimal  7 2  not null      INSERT INTO  temp SELECT   temp city         0         0         0 FROM     temp  INSERT  temp -- YTD shipments Charges Weight by city SELECT city   CASE                 WHEN nameaddrmstr 1 city IN   ABERDEEN    CHESAPEAKE    CHICAGO                                                                                               CLACKAMAS                                                 COLUMBUS    CONKLIN    DENVER                                                 FORT WORTH                                                 HANOVER PARK    JACKSONVILLE                                                 LAKELAND                                                                                               MONTGOMERY                                                                                                      RIVERSIDE    TRENTON    VERNON                  THEN                 CASE                   WHEN               nameaddrmstr 1 city    CHICAGO                AND h shipr    PREFRESVS  THEN  Preffered-Redist CHICAGO                  WHEN               nameaddrmstr 1 city    TRENTON                AND nameaddrmstr 1 city    CA  THEN  TRENTON CANADA                  ELSE               nameaddrmstr 1 city                 END                 ELSE  Other                END         ytdshipments   COUNT CONVERT VARCHAR 10   h dateshipped  101           ytdtotalweight  SUM CASE                              WHEN h totaldimwgt  gt  h totalwgt THEN h totaldimwgt                              ELSE h totalwgt                            END          ytdtotalcharges   SUM  cs totalestrevcharges  --YTDRevperPound   convert decimal 7 2  sum  cs TotalEstRevCharges   sum  CASE WHEN h TotalDimWGT  gt   gt  h TotalWGT THEN h TotalDimWGT ELSE h TotalWGT END    FROM   as400 dbo hawb AS h WITH nolock         INNER JOIN as400 dbo chargesummary AS cs          ON h hawbnum   cs hawbnum        LEFT OUTER JOIN as400 dbo nameaddrmstr AS nameaddrmstr 1          ON h shipr   nameaddrmstr 1 nameaddrcode WHERE  h dateshipped  gt    01 01 2010         AND h dateshipped  lt    12 19 2010         --WHERE H DateShipped  gt    gt    StartDate y AND H dateshipped  lt    EndDate         AND h cust IN   DARDENREED    MAINEDARDE    MBMRIVRSDE    MBMCOLUMBS                          MBMLAKELND    MBMFTWORTH    SYGMACOLUM    SYGMANETW6                          MAI215    MBMMNTGMRY    GROUP  BY CASE   WHEN nameaddrmstr 1 city IN   ABERDEEN    CHESAPEAKE    CHICAGO    CLACKAMAS                                   COLUMBUS    CONKLIN    DENVER    FORT WORTH                                   HANOVER PARK    JACKSONVILLE    LAKELAND                                   MONTGOMERY                                                                          RIVERSIDE    TRENTON    VERNON    THEN CASE                                                                          WHEN nameaddrmstr 1 city    CHICAGO  AND h shipr    PREFRESVS  THEN  Preffered-Redist CHICAGO                                                                           WHEN nameaddrmstr 1 city    TRENTON  AND nameaddrmstr 1 city    CA  THEN  TRENTON CANADA                                                                           ELSE nameaddrmstr 1 city                                                                        END   ELSE  Other  END  SELECT  temp city                 AS city         MAX  temp ytdshipments     AS ytdshipments         MAX  temp ytdtotalweight   AS ytdtotalweight         MAX  temp ytdtotalcharges  AS ytdtotalcharges FROM    temp WITH nolock         LEFT OUTER JOIN   temp          ON   temp city    temp city GROUP  BY  temp city  DROP TABLE  temp  DROP TABLE   temp

User · Answer

check your value which you want to store in integer column  I think this is greater then range of integer  if you want to store value greater then integer range  you should use bigint datatype

User · Answer

Use TRY CAST function in exact same way of CAST function  TRY CAST takes a string and tries to cast it to a data type specified after the AS keyword  If the conversion fails  TRY CAST returns a NULL instead of failing

User · Answer

My guess is that you re trying to squeeze a number greater than 99999 99 into your decimal fields  Changing it to  8 3  isn t going to do anything if it s greater than 99999 999 - you need to increase the number of digits before the decimal  You can do this by increasing the precision  which is the total number of digits before and after the decimal   You can leave the scale the same unless you need to alter how many decimal places to store  Try decimal 9 2  or decimal 10 2  or whatever   You can test this by commenting out the insert  temp and see what numbers the select statement is giving you and see if they are bigger than your column can handle

User · Answer

If you want to reduce the size to decimal 7 2  from decimal 9 2  you will have to account for the existing data with values greater to fit into decimal 7 2   Either you will have to delete those numbers are truncate it down to fit into your new size  If there was no data for the field you are trying to update it will do it automatically without issues

User · Answer

I feel I need to clarify one very important thing  for others  like my co-worker  who came across this thread and got the wrong information   The answer given   Try decimal 9 2  or decimal 10 2  or whatever    is correct  but the reason   increase the number of digits before the decimal   is wrong   decimal p s  and numeric p s  both specify a Precision and a Scale   The  precision  is not the number of digits to the left of the decimal  but instead is the total precision of the number   For example  decimal 2 1  covers 0 0 to 9 9  because the precision is 2 digits  00 to 99  and the scale is 1  decimal 4 1  covers 000 0 to 999 9  decimal 4 2  covers 00 00 to 99 99 decimal 4 3  covers 0 000 to 9 999

[sql] Arithmetic overflow error converting numeric to data type numeric

Examples related to sql

Examples related to sql-server-2008