SQL Server Arithmetic overflow error converting expression to data type int

Question

I m getting this error      msg 8115  level 16  state 2  line 18   Arithmetic overflow error converting expression to data type int    with this SQL query  DECLARE  year VARCHAR 4                          DECLARE  month VARCHAR 2                          -- START OF CONFIGURATION SECTION                        -- THIS IS THE ONLY SECTION THAT SHOULD BE MODIFIED                      -- SET THE YEAR AND MONTH PARAMETERS                          SET  year    2013                        SET  month    3    -- 1   January     12   Decemeber                          -- END OF CONFIGURATION SECTION                       DECLARE  startDate DATE                      DECLARE  endDate DATE                        SET  startDate    year    -     month    -01 00 00 00                        SET  endDate   DATEADD MONTH  1   startDate                           SELECT                               DATEPART YEAR  dateTimeStamp  AS  Year                                 DATEPART MONTH  dateTimeStamp  AS  Month                                 COUNT    AS NumStreams                                platform  AS  Platform                              deliverableName AS  Deliverable Name                             SUM billableDuration  AS NumSecondsDelivered                       FROM                                 DeliveryTransactions                         WHERE                                dateTimeStamp  gt    startDate                      AND dateTimeStamp  lt   endDate                         GROUP BY                                 DATEPART YEAR  dateTimeStamp                               DATEPART MONTH  dateTimeStamp                                 platform                                deliverableName                        ORDER BY                                  platform                              DATEPART YEAR  dateTimeStamp                                 DATEPART MONTH  dateTimeStamp                                deliverableName

User · Answer

Change SUM billableDuration  AS NumSecondsDelivered to  sum cast billableDuration as bigint    or   sum cast billableDuration as numeric 12  0    according to your need   The resultant type of of Sum expression is the same as the data type used  It throws error at time of overflow  So casting the column to larger capacity data type and then using Sum operation works fine

User · Answer

SELECT                               DATEPART YEAR  dateTimeStamp  AS  Year                                 DATEPART MONTH  dateTimeStamp  AS  Month                                 COUNT    AS NumStreams                                platform  AS  Platform                              deliverableName AS  Deliverable Name                             SUM billableDuration  AS NumSecondsDelivered   Assuming that your quoted text is the exact text  one of these columns can t do the mathematical calculations that you want   Double click on the error and it will highlight the line that s causing the problems  if it s different than what s posted  it may not be up there   I tested your code with the variables and there was no problem  meaning that one of these columns  which we don t know more specific information about  is creating this error   One of your expressions needs to be casted converted to an int in order for this to go through  which is the meaning of Arithmetic overflow error converting expression to data type int

User · Answer

Is the problem with SUM billableDuration   To find out  try commenting out that line and see if it works    It could be that the sum is exceeding the maximum int  If so  try replacing it with SUM CAST billableDuration AS BIGINT

User · Answer

declare  d real set  d 1 0  select  d 40000  192 2  20000 150000

User · Answer

Very simple   Use COUNT BIG    AS NumStreams

User · Answer

On my side  this error came from the data type  INT  in the Null values column  The error is resolved by just changing the data a type to varchar

[sql] SQL Server : Arithmetic overflow error converting expression to data type int

Examples related to sql

Examples related to sql-server

Examples related to tsql