Excluding invalid dates from query
-
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_Addedfrom (select * from ae_dt9 where isdate(field4) = 1) as tblwhere 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?
-
Have you tried looking at the data returned by the derived table to see if it's got any out-of-range dates?
If that doesn't help, try inserting into a temp table, then query the temp table, instead of a derived table. I've had problems with the query optimizer doing things like that until I break the query down a bit more for 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
-
It seems that the server replaces the derived table with the real table. I wrote a small script and got the same error as you got. You can see in the script that I have 3 records and one of the records has a value that is not a valid date. When I run the select statement I get the error. If I run only the select statement that defines the derived table, it brings back only the valid dates. If I check the query plan for the whole select statement, I can see that the filter is done in one step for all the criteria and that it isn’t checking first that the value is a date.
create table ae_dt9 (field4 varchar(20), numobjects int)goinsert into ae_dt9 (field4, numobjects)select '200807915', 1 --Not a valid dateunion select '20080115', 2union select '20080914', 1Select 9 as AppID,1 as M,2008 as Y,sum(numobjects) as pages_Addedfrom (select * from ae_dt9 where isdate(field4) = 1) as tblwhere month(tbl.field4) = '1' and year(tbl.field4) = '2008'The way that I found to prevent getting the error is to use case statement in the where clause, but this will not perform well with large tables.
Select 9 as AppID,1 as M,2008 as Y,sum(numobjects) as pages_Addedfrom (select * from ae_dt9 where isdate(field4) = 1) as tblwhere 1 = case when isdate(field4)= 1 then month(tbl.field4) else 2 endand 2008 = case when isdate(field4) = 1 then year(tbl.field4) else 0 endI have to admit that I am sorprised by the fact that the derived table is not getting proccessed first.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
The derived table not being processed first is exactly what I was refering to. That's why I've sometimes had to resort to a temp table instead of a derived table.
- 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
-
Thanks for your suggestions.
I have tried to add code to create a temp table at the top of the sproc and then change my Dynamic Sql to look at the temp table but I get an error back that the table does not exist.
This is the basic code
Declare @st as nvarchar(4000)set @st = 'Select docid, field4 into #cleanDT from ae_dt10 where isdate(field4) = 1'Exec sp_executesql @stSelect count(*) from #cleanDTBut the select count(*) statment always returns "Invalid object name '#cleanDT'".
If I just highlight the Select into statement and hit F5 it creates the #cleanDT table fine.
What am I doing wrong here?
-
If you use SELECT INTO with a temp table in dynamic sql, it won't be available to the proc that called it. Dynamic SQL launches in its own connection context.
Why are you using Dynamic SQL at all for this?
All you need to do is use a temp table instead of a derived table.
IE:
select *INTO #T1from ae_dt9where isdate(field4) = 1Select 9 as AppID,1 as M,2008 as Y,sum(numobjects) as pages_Addedfrom #t1where month(tbl.field4) = '1' and year(tbl.field4) = '2008'Of course, the better solution is likely to use a CASE statement like Michael showed you in your other thread. This is why you should not double post, it splits replies.
Something like this should work:
Select 9 as AppID,1 as M,2008 as Y,sum(numobjects) as pages_Addedfrom ae_dt9 where isdate(field4) = 1) as tblwhere 1 = CASE WHEN isdate(Field4) = 1 THEN MONTH(tbl.field4) ELSE 0 ENDAND2008 = CASE WHEN isdate(Field4) = 1 THEN YEAR(tbl.field4) ELSE 0 ENDWhile we're on the topic of posting correctly, please see the link in my signature on how to provide sample data for future posts. Had you done so, I'd have tested these responses rather than just throwing them out there off the top of my head.
-
Thanks Seth for taking the time to help me out.
I was using Dynamic SQL because the tablename is built is built dynamically based on an input parameter. Sometimes it is ae_dt10 and sometimes ae_dt11 etc.
I initially posted this to the wrong forum and then within about 5 min tried to delete it and repost here. But for some reason the old post is still there.
I am going to try the Case statement as described in the other post.
-
#1CoolGuy (1/15/2009)
Thanks Seth for taking the time to help me out.I was using Dynamic SQL because the tablename is built is built dynamically based on an input parameter. Sometimes it is ae_dt10 and sometimes ae_dt11 etc.
I initially posted this to the wrong forum and then within about 5 min tried to delete it and repost here. But for some reason the old post is still there.
I am going to try the Case statement as described in the other post.
Your dynamic SQL above didn't have any variables in it, or any reason to be Dynamic that you showed, which is why I was unsure of its purpose. You *can* use dynamic SQL here, you just can't use SELECT INTO.
This will work:
CREATE TABLE #CleanDT(DocIDint,Field4varchar(30))Declare @st as nvarchar(4000)set @st = N'Select docid, field4 from ae_dt10 where isdate(field4) = 1'-- Or set @st = N'Select docid, field4 from ae_dt' + @TableNumber + ' where isdate(field4) = 1'INSERT INTO #CleanDT(DocID, Field4)Exec sp_executesql @stSelect count(*) from #cleanDT
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply