October 29, 2008 at 10:40 am
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
October 29, 2008 at 1:00 pm
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.
October 29, 2008 at 1:32 pm
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]
October 30, 2008 at 4:08 am
ISDATE() is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
Failing to plan is Planning to fail
November 6, 2008 at 7:03 am
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.
November 6, 2008 at 7:31 am
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]
November 6, 2008 at 7:40 am
Madhivanan (10/30/2008)
ISDATE() is not reliablehttp://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]
November 7, 2008 at 7:58 am
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
November 7, 2008 at 8:19 am
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]
November 10, 2008 at 1:19 am
It returns zero rows
November 10, 2008 at 4:36 pm
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]
November 17, 2008 at 5:29 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply