SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Arithmetic overflow error converting expression to data type datetime.


Arithmetic overflow error converting expression to data type datetime.

Author
Message
raym
raym
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 307
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



dmc-608719
dmc-608719
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 787
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
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14730 Visits: 9518
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Madhivanan-208264
Madhivanan-208264
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 476
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
raym
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 307
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
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14730 Visits: 9518
We are going to need the column definition of [Firstdisbursementdate] and some examples of its typical content.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14730 Visits: 9518

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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
raym
raym
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 307
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
0
0
0
0
0
0



RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14730 Visits: 9518
OK, show us what you get from:

select distinct Firstdisbursementdate
from tbSales
where Len(Firstdisbursementdate) > 8



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
raym
raym
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 307
It returns zero rows



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search