Strange issue with CTE's and Dates as String

Question

Strange issue with CTE's and Dates as String

MikeM7

SSC Enthusiast

Points: 142
More actions
April 5, 2010 at 8:22 am

#233266

I've seeing a weird issue with a CTE when trying to convert a string column to a datetime. We have a table that has a column defined as a string but contains dates and other things. I wanted to create a CTE of the rows that contained dates and then use that CTE to further filter the list based on that date.
The following simplified example shows the issue. If you comment out the line "WHERE ObservationDate BETWEEN @StartDate AND @EndDate" it works fine but uncomment and you get a "Conversion failed" message. Is this a bug with CTEs or am I doing something wrong???
Thanks in advance for any help.
Regards,
Mike
SET NOCOUNT ON
DECLARE @StartDate DateTime
,@EndDate DateTime
SET @StartDate = '04/01/2008'
SET @EndDate = '04/1/2010'
CREATE TABLE tmp_TestTable
(
RunId int IDENTITY(1,1) NOT NULL,
Value varchar(35)
)
INSERT INTO tmp_TestTable VALUES ('N.A.')
INSERT INTO tmp_TestTable VALUES ('1/1/2010')
INSERT INTO tmp_TestTable VALUES ('2/1/2010')
INSERT INTO tmp_TestTable VALUES ('N.A.')
INSERT INTO tmp_TestTable VALUES ('3/1/2010')
INSERT INTO tmp_TestTable VALUES ('4/1/2010')
INSERT INTO tmp_TestTable VALUES ('5/1/2010')
;
WITH MaxDtCTE (RunId, ObservationDate) AS
(
SELECT RunId
,CONVERT(datetime, [Value])
FROM tmp_TestTable
WHERE ISDATE([Value]) = 1
AND [Value] IS NOT NULL
)
SELECT *
FROM MaxDtCTE
WHERE ObservationDate BETWEEN @StartDate AND @EndDate
DROP TABLE tmp_TestTable

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

SQL Server does not guarantee how many times an expression will be evaluated, or in which order...so long as the returned results logically reflect the request in the query.

In this case, SQL Server pushes the WHERE clause predicate up into the table scan, for performance reasons. The Filter operator that implements your ISDATE condition comes afterward. So, the date comparison is performed before the ISDATE, hence the error.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

MikeM7 SSC Enthusiast Points: 142 More actions · Answer 2

Thanks for the reply. But it still leaves me scratching my head for a way to code around this.

I changed the code around to use a table object but I can definitely see a performance hit. Do you have any ideas.

Thanks again. Mike

The Dr. SSC Veteran Points: 234 More actions · Answer 3

I added a case statement and it worked.

WITH MaxDtCTE (RunId, ObservationDate) AS

(

SELECT RunId,

CASE WHEN ISDATE([Value]) = 1 THEN CONVERT(datetime, [Value]) ELSE NULL END

FROM tmp_TestTable

WHERE ISDATE([Value]) = 1

AND [Value] IS NOT NULL

)

SELECT *

FROM MaxDtCTE

WHERE ObservationDate BETWEEN @StartDate AND @EndDate

Paul White SSC Guru Points: 150467 More actions · Answer 4

One safe way is:

SELECT TT.RunId,

CONVERT(DATETIME, TT.Value, 103)

FROM tmp_TestTable TT

WHERE TT.Value IS NOT NULL

AND 1 =

CASE

WHEN ISDATE(TT.Value) = 1

THEN

(

CASE

WHEN CONVERT(DATETIME, TT.Value, 103) BETWEEN @StartDate AND @EndDate

THEN 1

ELSE 0

END

)

ELSE 0

END;

edit: to add IS NOT NULL (not required for correctness, but can use an index)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

MikeM7 SSC Enthusiast Points: 142 More actions · Answer 5

MikeM7

SSC Enthusiast

Points: 142

April 5, 2010 at 11:38 am

#1145907

Thanks guys. Both suggestions are working.

-Mike