Arithmetic Overflow Error with Dates

[pengwyn]

I execute this procedure in Query Analyzer and it works. I execute it with a job and I get the following error in the job history.

Executed as user: UKKCPHPUK\SQLAgent. Arithmetic overflow error converting expression to data type smalldatetime. [SQLSTATE 22003] (Error 8115) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.

From Enterprise Manager stored procedurre list:
CREATE PROCEDURE [DD_BO_BacsVal]
as
BEGIN TRAN

TRUNCATE TABLE BO_BACSVAL

INSERT INTO BO_BACSVAL
SELECT
TOP 500 SUM(CONVERT(DECIMAL(5,2),ISNULL(a.quantity,0)))/5 "AvgTotQty",SUM(ISNULL(a.value,0))/5 "AvgTotBonus",
-- TOP 500000 SUM(CONVERT(DECIMAL(5,2),ISNULL(a.quantity,0)))/5 "AvgTotQty",SUM(ISNULL(a.value,0))/5 "AvgTotBonus",
MAX(x.quantity) "CurTotQty",MAX(x.value) "CurTotBonus",t.productid "ProductID",x.contractid "ContractID",
c.startdate "StartDate"
FROM
(SELECT MAX(ISNULL(quantity,0)) quantity,MAX(ISNULL(value,0)) value,productid,contractid FROM vwcreditdetail WHERE
periodid=(SELECT periodid-1 FROM tblperiod WHERE currentperiod=1)GROUP BY productid,contractid
UNION
SELECT NULL,NULL,b.productid,b.contractid FROM tblcontractproduct b
WHERE
not exists (select 'not exists' from vwcreditdetail a where a.productid=b.productid and
exists (select 'exists' from vwcreditdetail c where c.periodid=(SELECT periodid-1 FROM tblperiod WHERE currentperiod=1)
and c.contractid=a.contractid and c.contractid=b.contractid and c.periodid=a.periodid))
group by b.productid,b.contractid)X
,vwcontracts c,
dbo.vwcreditdetail a right outer join tblcontractproduct t on a.productid=t.productid
WHERE
a.periodid >= (SELECT DISTINCT CAST(SUBSTRING(CONVERT(VARCHAR,DATEADD(mm,-4,orderdate),112),1,6) AS INT) FROM tblorder
WHERE CAST(SUBSTRING(CONVERT(VARCHAR,orderdate,112),1,6) AS INT)=(SELECT periodid-1 FROM tblperiod WHERE currentperiod=1))
AND a.periodid<= (SELECT periodid-1 FROM tblperiod WHERE currentperiod=1)
AND t.productid=x.productid
AND a.contractid=x.contractid
AND a.contractid=c.contractid
and a.contractid=t.contractid
AND c.status='ca'
GROUP BY t.productid,x.contractid,c.startdate
ORDER BY t.productid,x.contractid,c.startdate

COMMIT
GO
I added the CAST portion of the statement as an initial attempt to resolve the problem.

Does anyone have any suggestions?

 

[pengwyn]

periodid is an INT. I added the CAST( )AS INT function hoping that would convert the varchar substring into an INT.

Should I use the CONVERT function instead like below?

a.periodid >= (SELECT DISTINCT CONVERT(INT,(SUBSTRING(CONVERT(VARCHAR,DATEADD(mm,-4,orderdate),112),1,6)))
FROM tblorder
WHERE CONVERT(INT,(SUBSTRING(CONVERT(VARCHAR,orderdate,112),1,6)))

 

[SQLBill]

Nope, your CONVERT should be good. I didn't know periodid was INT.

Another thought.... c.startdate "StartDate". StartDate is NVARCHAR as you stated above....but in BO_BACSVAL, what is that column's datatype? SMALLDATETIME? If so, that could be where the problem is.

-SQLBill

Posting advice: FAQ481-4875

 

[pengwyn]

Thanks!

I have made the change to CAST(c.startdate as SMALLDATETIME) "StartDate"

And that seems to have worked. The job runs successfully now.

All help has been much appreciated!

 

[SQLBill]

Sure did take us a while, but I'm glad we finally got your script running.

-SQLBill

Posting advice: FAQ481-4875