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?

 

[vongrunt]

Post structure for table BO_BACSVAL.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pengwyn]

Here is the table structure for BO_BACSVAL:
AvgTotQty, decimal, 9(10,2)
AvgTotBonus, decimal, 9(10,2)
CurTotQty, decimal, 9(10,2)
CurTotBonus, decimal, 9(10,2)
ProductID, varchar, 50
ContractID, int, 4
StartDate, smalldatetime,4

 

[vongrunt]

StartDate is smalldatetime, and it gets filled from vwcontracts.startdate.

Check this column. Is it datetime or (ouch!) varchar? If true, then it is possible that some of returned values are out of smalldatetime domain (Y1900-2079).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pengwyn]

It's OUCH!!
vwcontracts.startdate is nvarchar(20).

 

[vongrunt]

In exactly what format (few sample values please)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pengwyn]

The format for start date is:

1/4/2003
1/11/2003

 

[vongrunt]

This query will return all rows with startdate outside smalldatetime range:

set dateformat mdy

select *
from vwcontracts
where isdate(startdate) = 1 
	and convert(datetime, startdate) not between '19000101' and '20790606'

From two sample values dateformat is still not clear (mdy or dmy?) so set this to appropriate value.

Is there any record returned by this query?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pengwyn]

The date format is dmy
30/11/2003
14/11/2003

The query did not return any records.

 

[SQLBill]

Did you change vongrunt's code to begin with
SET DATEFORMAT DMY
before you ran it?

-SQLBill

Posting advice: FAQ481-4875

 

[pengwyn]

Yes, I added the statement set dateformat dmy before I ran the query.

 

[SQLBill]

It could be some dates were entered MDY format by mistake. I suggest writing a script that will 'grab' the part between the two /'s and returning any that are greater than 12. If it returns anything, those are in the wrong format.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

I think ISDATE() can handle this:

set dateformat mdy

select *
from vwcontracts
where isdate(startdate) = 0
-- and startdate is not null
-- and startdate <> ''

Uncomment last two conditions if necessary (to ignore rows with blank values).


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Damn... set dateformat dmy .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pengwyn]

I ran the script
1 as set dateformat mdy
2 as set dateformat dmy

select *
from vwcontracts
where isdate(startdate) = 0
and startdate is not null
and startdate <> ''

With the dateformat set as mdy I get 35 rows returned when the nulls and blanks are filtered out and 1299 rows without.

With the dateformat set as dmy I get no rows returned when the nulls and blanks are filtered out and 1264 rows without.

 

[SQLBill]

So you have lot's of NULLs or Blanks (or both). That means that when those values are converted to DATETIME, they become the default (Jan 1, 1900). That could be causing your problem. One way to test it....in the spot where you convert the dates, replace the conversion with a valid date from your column. See what happens, then run it with the default date.

-SQLBill

Posting advice: FAQ481-4875

 

[pengwyn]

Are you saying where I have the statements
substring(convert(varchar,dateadd(mm,-4,orderdate),112)1,6)
and
substring(convert(varchar,orderdate,112),1,6)

I should replace those with a value like 30112004 in mdy format?

 

[SQLBill]

I'm confused here....for the rest of this thread we were discussing STARTDATE. Now you are talking about ORDERDATE.

What datatype is ORDERDATE? If VARCHAR, what does the values look like? Like you show above or are they stored 30/11/2004??

Here's what you should do:

replace ORDERDATE with an actual value from your column:

substring(convert(varchar,dateadd(mm,-4,'30/11/2004'),112)1,6)

Do that for ALL ORDERDATE values. Then run the script. Does it work? Then replace that value with the default 1/1/1900.

-SQLBill

Posting advice: FAQ481-4875

 

[pengwyn]

I am a bit confused myself.

The field startdate in vwcontracts is nvarchar(20) and the data is stored in the table in dmy format. (i.e. 14/1/2004, 21/1/2004)

The field orderdate in tblorder is smalldatetime and the data is stored in the table in ymd format. (i.e. 2005-01-21 09:18:00, 2004-11-19 12:07:00)

I ran the following query:
SELECT DISTINCT SUBSTRING(CONVERT(VARCHAR,DATEADD(mm,-4,orderdate),112),1,6) FROM tblorder
Replacing orderdate with 2004/11/19 and 11/19/2004 the result is 200407. (dateformat mdy)
If I set dateformat dmy and replace orderdate with 19/11/2004 the result is also 200407.
Replacing orderdate with 01/01/1900 the result is 189909.

 

[SQLBill]

Okay....I think I may have figured out the problem.

Start with this:

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

I bet periodid is SMALLDATETIME. You are comparing SMALLDATETIME to a VARCHAR value in both lines (see bolded items).

So SQL Server is trying to convert 200407 or whatever back to SMALLDATETIME so it can compare it to the periodid value.

Or at least that appears to be the issue to me. Try CONVERTING periodid to the same format.

-SQLBill



Posting advice: FAQ481-4875