Date conversion: SELECT goes well, WHERE fails.

Passiday ( ) writes:
> So I have spent now couple of hours tearing my hair out about this
> problem.
>
> So, I have this set of data - table with string field in format
> 'DDMMYY-ABCD', where DDMMYY points to the date of the document. Sample
> field value would be 100610-47234. I want to filter the data by this
> date, so I need to convert it to date value.
>
> This is how I do it:
>
> SELECT ..
> CONVERT(datetime, '19' + SUBSTRING(documentCode, 5, 2) + '-' +
> SUBSTRING(documentCode, 3, 2) + '-' + SUBSTRING(documentCode, 1, 2),
> 120) documentDate
>
> SELECTing this in query analyzer goes just fine. Lot's of rows, seem
> to be nicely converted.
>
> Now, if I add to the query criteria
> WHERE documentDate < '1945-06-01' (I enclosed the query above in a
> subquery so that I can filter by the calculated field), I get this
> error:
>
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.

To find bad dates:

WITH CTE (
SELECT ... , documentDate as above
...
)
SELECT *
FROM CTE
WHERE isdate(documentDate) = 0


However, I would suspect that the date that is blowing up is
1945-06-01. This is not a safe format, and if you are in Europe it
is not unlikly that your dateformat setting is dmy. Try using
'19450601' instead.

--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails. 

Hello,

The suggestion by Marlacoba was just irrelevant, sorry. You didn't
care to read my message, just assuming askers stupidity. I am
converting the date from string, before attempting to filter by that
date. Besides, statement where chardate < '100610' will match also
'060499', because those are strings that are compared by their
alphabetical order.

Erland, you suggestion didn't help. I figure that the problem lies in
how MSSQL server failed when executing the query. My inner query
filters off the documents whose first 6 digits give bad date, but it
seams that for some reason, the date conversion expression is executed
also on the bad rows, when that field is used in the WHERE criteria
outside.

I have managed to get rid of that stupid error by using temporary
table - I establish empty table, then fill it with the data including
also that converted date (works, because the query doesn't break when
not using the date in the where criteria), and then query the temp
table, filtering by the date. By that time MSSQL engine has forgot
from where the date is sourced and doesn't do any foolish calculations
on invisible rows.

Working solution, but not satisfactory. Using temp table is too heavy
for this simple purpose.
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails. 

> You didn't care to read my message, just assuming askers stupidity.

That's fair I should have been more clear in my inference. I was attempting
to suggest that you post DDL and sample data.

Dan
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails. 

Passiday ( ) writes:
> Erland, you suggestion didn't help. I figure that the problem lies in
> how MSSQL server failed when executing the query. My inner query
> filters off the documents whose first 6 digits give bad date, but it
> seams that for some reason, the date conversion expression is executed
> also on the bad rows, when that field is used in the WHERE criteria
> outside.

It was a little difficult to since you posted your queries in fragments.
There were two possible problems, and I picked the wrong one. Or only
one of them.

You need this in the WHERE clause:

CASE isdate(documentDate) WHEN 1 THEN documentDate END < '19450601'

The CASE expression is the only reliable way to filter out data that
is not converting. (Or causes some other accidents like division by 0.)

Even if your query is logically correct, the optimizer is taking liberties
which ruins your plans.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails. 

Hi,

> It was a little difficult to since you posted your queries in fragments.
> There were two possible problems, and I picked the wrong one. Or only
> one of them.

Well, yes, the problem is that I can not repeat this problem with
sample data. This behavior apparently is connected to the specific
setup of the database and table indexes, etc.

Anyway, here's the sample code that works just fine. You just must
take my word that on the real data (the table of course has more
fields and has some indexes attached, etc), the INSERT fails.

DECLARE @Documents TABLE
(
docId int,
docNumber varchar(12),
docNotes nvarchar(30)
)

INSERT INTO @Documents (docId, docNumber, docNotes) VALUES (1,
'010345-99999', 'Good id')
INSERT INTO @Documents (docId, docNumber, docNotes) VALUES (2,
'01x377-99999', 'Bad id')

SELECT
docId,
docDate
FROM
(
SELECT
*,
CONVERT(datetime, '19' + SUBSTRING(docNumber, 5, 2) +
SUBSTRING(docNumber, 3, 2) + SUBSTRING(docNumber, 1, 2)) docDate
FROM
@Documents
WHERE
docNumber LIKE '[0-3][0-9][0-1][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]
[0-9]'
) DocumentsPlus
--WHERE docDate < ' 1945-06-01' -- Unfiltered SELECT works fine, but
the filtered one fails (but only on the real data. This sample code
works fine, but is provided here for clarity)

> You need this in the WHERE clause:
>
>    CASE isdate(documentDate) WHEN 1 THEN documentDate END < '19450601'
>
> The CASE expression is the only reliable way to filter out data that
> is not converting. (Or causes some other accidents like division by 0.)

Even this didn't work out. It seems that the optimizer really wants to
convert the date in the inner query, even though the bad dates are
filtered out. Yes, I am aware that my LIKE expression allows values
like 391911-12345, but I can ensure, there are no such bad Ids. After
all, the SELECT works fine, when not filtered by date.

> Even if your query is logically correct, the optimizer is taking liberties
> which ruins your plans.

Yes, this really appears to be optimizers fault. I guess there should
be some table hints to tell the optimizer what to do? Well, anyway,
that sounds really stupid - the optimizer makes this error only when
the date is used for filtering. It turns out that the engine has
decided that it would ignore the WHERE criteria in the inner query
that filters out the bad Ids. Bringing that criteria out doesn't
change things either.

Seems like I have lost this fight. Temporary table works fine, so I'll
stick with it. This had to be small simple thing, and I don't have
more time to invest in deep research why this happens so. Unless
someone had some smart idea, of course.

Thanks,

Passiday
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails. 

Passiday ( ) writes:
> Even this didn't work out. It seems that the optimizer really wants to
> convert the date in the inner query,

Well, you are telling it to!

> even though the bad dates are filtered out. Yes, I am aware that my LIKE
> expression allows values like 391911-12345, but I can ensure, there are
> no such bad Ids. After all, the SELECT works fine, when not filtered by
> date.

You need to take the convert out of the query, and then do the convert
in the THEN clause in the CASE expression.

Then again, if the strings you produce in the virtual table are YYYYMMDD,
you could just as well do:

SELECT docId,
CASE WHEN isdate(docDate) = 1 THEN convert(datetime, docDate) END
FROM (SELECT *, '19' + SUBSTRING(docNumber, 5, 2) +
SUBSTRING(docNumber, 3, 2) +
SUBSTRING(docNumber, 1, 2)) docDate
FROM @Documents
WHERE docNumber
LIKE '[0-3][0-9][0-1][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'
) DocumentsPlus
WHERE docDate < '19450601'
AND isdate(docDate) = 1

You still need the CASE expression in the SELECT list, because the
optimizer may compute the expressions in the SELECT list before the
filtering.

The comparison here is a string comparison, but it works since the format
is YYYYMMDD.



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Date conversion: SELECT goes well, WHERE fails.