question

Jclayto1 avatar image
Jclayto1 asked

Arithmetic overflow error for type int, value = 3911549527.120030.

I am trying to pull the "Period" using referencing dates between two tables. Both tables have dates which are represented as 'dd-MM-yy'.

However, whenever I execute it gives me an arithmetic overflow error for type int. I have tried casting and converting to nvarchar. Nothing has worked.

The error only appeared after trying to inner join, when excluding this statement the query runs fine.

See below query.

SELECT so.BranchId,

year(InvoiceDate) as 'Year',

datepart(week,InvoiceDate) as 'Fiscal Week',

concat(so.branchid,'-', datepart(week,InvoiceDate)) as 'Primary Key',

cast(sum(TotalPounds) as INT) as 'Pounds Shipped',

cast(sum(sum(TotalPounds)) OVER (PARTITION BY so.branchID,year(InvoiceDate) ORDER BY datepart(week,InvoiceDate)) as int) as 'Running Total'

FROM SalesOrders as so

INNER JOIN GLTransactions as gl

ON convert(varchar,InvoiceDate,105) = convert(varchar,EntryDate,105)

WHERE year(invoicedate) = 2021 and BillTo != '6296'

GROUP BY so.BranchID,

year(InvoiceDate),

datepart(week,InvoiceDate)

ORDER BY so.BranchID,

year(InvoiceDate),

datepart(week,InvoiceDate);

error-message
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

Something is overflowing the int, so use BIGINT instead.

It will most be one of the sums.


Also store dates as dates not strings the converting to varchar 105 is just asking for headaches. Dates are numbers not words.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.