January 14, 2009 at 1:01 pm
I have a table that I have no control over the structure of from which I need to pull data.
The datatype of field4 is varchar(20) but it is intended to hold the creation date of an object which should always be able to be cast to a datetime datatype.
I have found that there are some bad values in the table such as '1097-06-07' my query needs to simply exclude these bad values from teh results.
The following,
Select 9 as AppID,
1 as M,2008 as Y,
sum(numobjects) as pages_Added
from (select * from ae_dt9 where isdate(field4) = 1) as tbl
where month(tbl.field4) = '1' and year(tbl.field4) = '2008'
however, returns the error message
'Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'
Shouldn't the isdate function exclude any records from field4 that could potentially result in this error?
How can I get it to just skip those records that would result in a conversion error?
January 14, 2009 at 1:48 pm
I would imagine that the ISDATE() Function should help you greatly although I would imagine that the performance would suffer quite a bit...
Any chance you could get us some sample data of rows you know are causing problems?
Perhaps some DDL for the Table as well?
this may be a case of you needing to run your subquery and reviewing the records you get back to see what is going on...
Also, what version of SQL are you using?
-Luke.
January 14, 2009 at 1:56 pm
This thread is a cross-post. Please reply to the other copy, in T-SQL forum.
(As an aside, it already has IsDate in it. That won't solve it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 2:16 pm
It looks like the Optimizer is ignoring your subquery since you are not referencing anything outside the table. Basically it is converting your query to this:
Select
9 as AppID,
1 as M,
2008 as Y,
sum(numobjects) as pages_Added
from
ae_dt9
where
isdate(field4) = 1 And
month(tbl.field4) = '1' and
year(tbl.field4) = '2008'
You'll need to use a table variable or temp table to filter out the invalid dates, like this:
Declare @test table(date varchar(25))
Insert into @test
Select '1097-06-07'
union
select Convert(varchar(25), getdate(), 111)
Declare @valid_dates table(date varchar(25))
Insert Into @valid_dates
Select
date
from
@test
Where
isdate(date) = 1
Select * From @valid_dates where month(date) =1
-- these don't work because the optimizer and processor process them like a single table query
;With cteValidDates
AS
(
Select
date
from
@test
Where
isdate(date) = 1
)
Select
*
From
cteValidDates
Where
month(date) = 1
Select
date,
isdate(date),
month(date)
From
(select date from @test where isdate(date) = 1) as t
Where
Month(T.date) = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 14, 2009 at 2:17 pm
Yes, Jack, that's what I already suggested on the other copy of this same question. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 3:03 pm
This way will prevent SQL Server from bypassing the check.
Case statement conditions are evaluated one at a time, and processing of the case stops on the first true condition.
Select
9 as AppID,
1 as M,
2008 as Y,
sum(numobjects) as pages_Added
from
ae_dt9 as tbl
where
1 =
case
when field4 is null
then 0
when isdate(field4) <> 1
then 0
when month(tbl.field4) <> 1
then 0
when year(tbl.field4) <> 2008
then 0
else 1
January 14, 2009 at 6:24 pm
GSquared (1/14/2009)
Yes, Jack, that's what I already suggested on the other copy of this same question. 🙂
I missed that one.
Michael has an interesting solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply