Arithmetic overflow error converting expression to data type datetime.

Question

Arithmetic overflow error converting expression to data type datetime.

raym

SSCrazy

Points: 2213
More actions
October 29, 2008 at 10:40 am

#192790

I have table tbSales where the is datatype numeric(8,0). When I run this script I
keep getting this error :-
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.
DECLARE @FirstDay DATETIME,@LastDay DATETIME
SET @FirstDay =(SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-0,0 ))
SET@LastDay =(SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE())-0,-1))
SELECT
Loanreference
,BranchCode
,Firstdisbursementdate
,LoanOfficerCode
,[#ofLoans]
,LoanId
,IDNumber
,ClientNumber
,StartDate
,CreationDate
,CreatedBy
FROM
dbo.tbSales
WHERE CONVERT(DATETIME,Firstdisbursementdate,120) BETWEEN @FirstDay AND @LastDay
AND ISDATE(Firstdisbursementdate) = 1
I have tried to filter out the invalid dates which are just Zeros but I keep getting this error.
How do I solve this

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

I would look at the isdate() in the where clause. Is it possible that the query evaluates the first condition and fails, before it eliminates the isdate() criteria. Maybe filter by isdate() into a temp table or result set and then apply the next criteria to that.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Try it like this:

DECLARE @FirstDay DATETIME,@LastDay DATETIME

SET @FirstDay =(SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-0,0 ))

SET@LastDay =(SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE())-0,-1))

SELECT

Loanreference

,BranchCode

,Firstdisbursementdate

,LoanOfficerCode

,[#ofLoans]

,LoanId

,IDNumber

,ClientNumber

,StartDate

,CreationDate

,CreatedBy

FROM

dbo.tbSales

WHERE Case When ISDATE(Firstdisbursementdate) = 1

Then CONVERT(DATETIME,Firstdisbursementdate,120)

Else Cast(0 as datetime) End

BETWEEN @FirstDay AND @LastDay

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

ISDATE() is not reliable

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

raym SSCrazy Points: 2213 More actions · Answer 4

I tried using

FROM

dbo.tbSales

WHERE Case When ISDATE(Firstdisbursementdate) = 1 AND LEN(Firstdisbursementdate)>= 8

Then CONVERT(DATETIME,Firstdisbursementdate,120)

Else Cast(0 as datetime) End

BETWEEN @FirstDay AND @LastDay and I still get the same error..

Msg 8115, Level 16, State 2, Line 4

Arithmetic overflow error converting expression to data type datetime.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

We are going to need the column definition of [Firstdisbursementdate] and some examples of its typical content.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Madhivanan (10/30/2008)
ISDATE() is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

I did read the article at your blog, but there were no examples of ISDATE() being unreliable. If you could supply some examples of ISDATE() returning results inconsistent with what CONVERT(DateTime, {string}, style) can convert successfully (asuming style is not 0,9,100, or 109), that would be helpful. However, for now, ISDATE appears to be perfectly reliable to me.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

raym SSCrazy Points: 2213 More actions · Answer 7

Hi I did a check the non date valuse are zeros ......used isdate to determine non valid dates

--select distinct Firstdisbursementdate

--from tbSales

--where isdate(Firstdisbursementdate) =1

--select distinct Firstdisbursementdate

--from tbSales

--where isdate(Firstdisbursementdate) !=1

Valid dates Firstdisbursementdate

20020405

20041222

20001111

20060223

19991110

19980425

19991118

20080723

20020813

Non Valid dates Firstdisbursementdate

0

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

OK, show us what you get from:

select distinct Firstdisbursementdate

from tbSales

where Len(Firstdisbursementdate) > 8

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

raym SSCrazy Points: 2213 More actions · Answer 9

raym

SSCrazy

Points: 2213

November 10, 2008 at 1:19 am

#896143

It returns zero rows

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Is this still not working? What is the current state of things?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

I don't know if anyone has given you the Stern Lecture for using a non-datetime datatype to contain datetime data, but if not, consider it done.

That being said, and with the realization that we can't always control the form of the data sent to us from outside our domain, here is one solution. I've even fixed the IsDate problem. By using date arithmetic rather than conversion (limiting CONVERT to change the numeric to integer so we can use modulo), all values between the two extremes will generate a valid date -- although, if someone entered the date 20080231 which is not a valid date, the result will be 2008-02-29, which is a valid date but not necessarily the correct date (the date the user meant to enter). Since this will not result in an error, it may not be the result you want.

declare @test-2 table(

FirstDispursementDate numeric( 8, 0 )

);

Insert @test-2( FirstDispursementDate )

select 20020405 union all

select 20041222 union all

select 20001111 union all

select 20060223 union all

select 19991110 union all

select 19980425 union all

select 19991118 union all

select 20080723 union all

select 20020813 union all

select 17500723 union all -- Bogus date

select 20070231 union all -- Looks bad but converts to 2007-02-28

select 20029999 union all -- Looks bad but converts to 2010-06-09

select 20029901 union all -- Looks bad but converts to 2010-03-01

select 99991231 union all

select 0; -- Bogus date

-- All calculations in one statement

select FirstDispursementDate as AsNumeric,

DateAdd( mm, ((Convert( int, FirstDispursementDate / 10000 ) - 1900) * 12 )

+ ((Convert( int, FirstDispursementDate ) % 10000) / 100) - 1,

DateAdd( dd, (Convert( int, FirstDispursementDate ) % 100) - 1, 0 ))

as AsDatetimeValue

from @test-2

where FirstDispursementDate between 17530101 and 99991231; -- Fullproof "IsDate" function

-- The same calculations but separated into nested derived tables for purposes

-- of illustration only.

-- First convert numeric to int (x), then split int value into three values for

-- year, month and day (y) and finally manipulate to make a datetime value.

select DateAdd( mm, ((FDDYear - 1900) * 12) + FDDMonth - 1,

DateAdd( dd, FDDDay - 1, 0 )) as AsDatetimeValue

from(

select FirstDispursementDate / 10000 as FDDYear,

FirstDispursementDate % 10000 / 100 as FDDMonth,

FirstDispursementDate % 100 as FDDDay

from(

select Convert( int, FirstDispursementDate ) as FirstDispursementDate

from @test-2

where FirstDispursementDate between 17530101 and 99991231

) x

) y;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms