Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Arithmetic overflow error with dates

Status
Not open for further replies.
bubarooni

bubarooni

Technical User
Joined
May 13, 2001
Messages
506
Location
US
please let it be the parantheses in the wrong place!

I have a table called aropen. In it is a field called duedt. All I want to do is pull every record where duedt is greater than 120 from today.

duedt [decimal[8,0], Not Null]

I try:

SELECT * FROM dbo.AROPEN Where CONVERT(decimal(8), GetDate(), 112) - CONVERT(decimal(8), duedt, 112) > 120

Unfortunately, this only brings up ten junk records where the value of the field wasn't set to a date somehow and is a 0.

Flailing about I try:

SELECT * FROM dbo.AROPEN Where Cast(CONVERT(decimal(8), GetDate(), 112) as dateTime) - Cast(CONVERT(decimal(8), duedt, 112) as dateTime) > 120

but then get an error message:

Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.

I also tried DATEDIFF with no results.

Thanks In Advance for any help. I couldn't find any in that bol or google.
 
Sort by date Sort by votes
Code: 
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] dbo.AROPEN 
   [Blue]WHERE[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray][Blue]dd[/Blue][Gray],[/Gray]0[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray]-[/Gray]duedt[Gray])[/Gray][Gray]>[/Gray]120
This solution implies that 120 days times 24 hours times 60 minutes times 60 seconds has to have transpired to be included in the result. (ESquared hehe)
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
But it's generally more efficient to leave the datetime column on one side of the equation so that an index, if it exists, can be used.
Code: 
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] dbo.AROPEN 
   [Blue]WHERE[/Blue] duedt[Gray]<[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray]-[/Gray]120
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
Dang! Tried them both with no success. Same artithmetic overflow erro.

the following select statement:

select duedt, getdate() from dbo.aropen

returns records like this:

duedt getdate()
20041225 2005-2-23 07:20:23.870

I think I get the error from the duedt field and need to convert it to something that can perform math. I'll look in that bol thing again.

Thanks

 
Upvote 0 Downvote
Code: 
[Blue]SET[/Blue] [Blue]DateFormat[/Blue] ymd
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] dbo.AROPEN 
   [Blue]WHERE[/Blue] [Fuchsia]Cast[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]8[Gray])[/Gray][Gray],[/Gray]duedt[Gray],[/Gray]112[Gray])[/Gray] [Blue]AS[/Blue] [Blue]datetime[/Blue][Gray])[/Gray]
        [Gray]<[/Gray] [Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray]-[/Gray]120
If this doesn't work then you have bad date data in duedt. If you do it serves your right for storing it that way! :-)
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
If you do it serves your right for storing it that way!
Click to expand...

[bandito] [blue]DBomrrsm[/blue] [bandito]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]
 
Upvote 0 Downvote
I think it can be read either way, no?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Upvote 0 Downvote
well, my main experience is with access db's where i store in a date/time format. this is an application from a vendor. unfortunately it is one of the two major applications the company runs on. serves me right for working here.
 
Upvote 0 Downvote
One thing to remember with values that you want to convert to DATETIME. SQL Server has to know how to interpret the value to be converted.

20040102

Is that Jan 02 2004 or 1 Feb 2004?

Setting the DATEFORMAT at the beginning of a script helps.

Code: 
SET DATEFORMAT YMD
<rest of script>

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
I execute this procedure in Query Analyzer and it works. I execute it with a job and I get the foollowing error The only difference is thtt if you look at the query in query Analyzer and the proceudu starts out with 'Alter Procedure' and in the Enterprise manager procedure list it starts out with /Create Table.

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
=======================================================
From Query Anallyzer stored procedue list:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================
Does anyone have any suggestions?


 
Upvote 0 Downvote
First suggestion - start a new thread with this question. When you do, post the actual error message you are getting.

Oh, and don't worry about the different ALTER/CREATE statements. That's just how Ent Mgr displays it.

--James
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek
Virendra Yaduvanshi
  • Locked
  • Question Question
SSRS - Subscription page error
Replies
0
Views
560
Virendra Yaduvanshi
Virendra Yaduvanshi
Cpreston
  • Locked
  • Question Question
Update with a month name
Replies
9
Views
2K
gmmastros
gmmastros
barim
  • Locked
  • Question Question
Compare Dates With Current Date
Replies
3
Views
363
barim
barim

Part and Inventory Search

Sponsor

Back
Top