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.
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?