Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Arithmetic overflow error converting expression to data type datetime. Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 10:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, 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



Post #593785
Posted Wednesday, October 29, 2008 1:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
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.
Post #593875
Posted Wednesday, October 29, 2008 1:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #593894
Posted Thursday, October 30, 2008 4:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
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
Post #594176
Posted Thursday, November 06, 2008 7:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, 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.



Post #598088
Posted Thursday, November 06, 2008 7:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #598117
Posted Thursday, November 06, 2008 7:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479

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."
Post #598131
Posted Friday, November 07, 2008 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, 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



Post #598966
Posted Friday, November 07, 2008 8:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #598986
Posted Monday, November 10, 2008 1:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, Visits: 307
It returns zero rows


Post #599675
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse