I had to write a SQL query, that would return the records where the column "ImportFileName" started with the year "2013"
-- Drop temp table if it exists. if object_id('tempdb..#Import') is not null begin drop table #Import end -- Create temp table. create table #Import ( Id int not null, ImportFileName varchar (255) not null ) -- Seed temp table. insert into #Import (Id, ImportFileName) values (1, '20130506101010.csv'), (2, '20130606101010.csv'), (3, 'This_is_an_error_file.csv'), (4, '20130806101010.csv'), (5, '20130906101010.csv') -- Query the temp table select Id ImportFileName from #Import where datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013 -- Drop temp table. drop table #Import
This query results in the error:
Msg 241, Level 16, State 1, Line 19 Conversion failed when converting date and/or time from character string.
This can be fixed with the SQL Server 2012 and above function: TRY_CONVERT:
-- The fixed query (>= SQL Server 2012) select Id ImportFileName from #Import where datepart(year, try_convert(date, substring(ImportFileName, 1, 8))) = 2013
But in SQL Server 2008R2 this function does not exist, so I tried to trick SQL Server by using a subquery that would only return valid records:
-- Query the temp table select i.Id, i.ImportFileName from ( select Id, ImportFileName from #Import where isdate(substring(ImportFileName, 1, 8)) = 1 ) i where datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013
But this resulted in the same error, why????
Well the estimated execution plan tells you why:
Effectively SQL Server query optimization will merge the two where statements and convert the query above to something like:
select Id, ImportFileName from #Import where datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013 and isdate(substring(ImportFileName, 1, 8)) = 1
which will fail.
To get around this problem, you could first insert the subquery records in a temp table and then check query the temp table, but in my case I used a case statement:
-- Query the temp table select i.Id, i.ImportFileName from ( select Id, case when isdate(substring(ImportFileName, 1, 8)) = 1 then ImportFileName else null end as ImportFileName from #Import ) i where datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013
The full script now look like:
-- Drop temp table if it exists. if object_id('tempdb..#Import') is not null begin drop table #Import end -- Create temp table. create table #Import ( Id int not null, ImportFileName varchar (255) not null ) -- Seed temp table. insert into #Import (Id, ImportFileName) values (1, '20130506101010.csv'), (2, '20130606101010.csv'), (3, 'This_is_an_error_file.csv'), (4, '20130806101010.csv'), (5, '20130906101010.csv') -- Query the temp table select i.Id, i.ImportFileName from ( select Id, case when isdate(substring(ImportFileName, 1, 8)) = 1 then ImportFileName else null end as ImportFileName from #Import ) i where datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013 -- Drop temp table. drop table #Import
What’s wrong with: select * from #Import where ImportFileName like ‘2013%’? 😉
Or a with a bit of “validation”:
select * from #Import
where isnumeric(left(ImportFileName,4)) = 1
and left(ImportFileName,4) = 2013