Date comparsion within subquery fails with: "out-of-range value"

Question

I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid.

CREATE TABLE [dbo].[input](
 [id] int,
 [START_DATE] [varchar](30) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

In the sample data only record 4 is valid.

My query:

select a.[id] 
FROM (
 select [id], convert(datetime, [START_DATE],106) as csd
 FROM [dbo].[input]
 where len([START_DATE]) > 0 
 and substring([START_DATE],7,5) > 2010
 and isdate([START_DATE]) = 1
 ) a
 Where a.csd < getdate()

I've had a play with various clauses to exclude junk:

• len> 0 excludes nulls and ''.
• Substring looks for a sensible year.
• isdate checks again for a valid date.

I'm sure there's other more efficient ways of doing this, but the point being the subquery executes and correctly returns valid records. in this case id = 4. Which has a valid date 1 July 2016.

The outer query should simply now be comparing the converted date with today's date. But instead i get:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Why? What am i missing?

The inner query has filtered and converted the value I am evaluating.

Is there a better way of filtering this list to be used in an update query?

Answer 1

The execution order is in much regards up to the query optimizer. So in your case, even thou the SQL code you wrote looks like the subquery would filter out the invalid dates first, what the actual execution of the query looks like, can be very different.

You can run your query with option(force order), but be careful with that one, very careful, and know what you are actually doing. Another option would be a temp table, to preselect the valid or the invalid dates, but that could be very costly as well.

While it feels dirty, and it is, you could also compare string values, to not run into this issue, just make sure to use a codepage that will sort as a string as it would as a date

select a.[id] 
FROM (
 select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
 FROM [dbo].[input]
 where len([START_DATE]) > 0 
 and substring([START_DATE],7,5) > 2010
 and isdate([START_DATE]) = 1
 ) a
 Where a.csd < convert(varchar(23, getdate(), 121)

all in all, it would be best to save the start_date as an actual date type thou.

• Sir Swears-a-lot: I've had a good play with this with No joy. I've various Hints, Force order table hints, adding index and using index hints, with no success. I think I'll have to go with your suggestion of a temp table.
• Sir Swears-a-lot: I split out the subquery to insert to a temp table and now it works. Thanks :)