Sql-server – Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

sql serversql-server-2008-r2

I have a simple select select statement, see below. 

SELECT     JobTasks.JobTaskID, 
           CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) 
FROM       JobTasks 
INNER JOIN JobExecutions 
ON         JobTasks.JobExecutionID = JobExecutions.JobExecutionID 
INNER JOIN JobStatus 
ON         JobExecutions.JobStatusID = JobStatus.JobStatusID 
WHERE      (JobExecutions.JobID = 1) 
AND        (JobTasks.FileName IS NOT NULL)    
ORDER BY   CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20)

It fails on converting the varchar to a date time, but if I change the select part of the script to include the filename field without the convert part it never fails, like below: 

SELECT JobTasks.JobTaskID, CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20), JobTasks.FileName

I have checked the data and it is all correct and can be converted to a datetime without issue. I have stripped out all the non numeric characters from the data and converted them all to datetime using CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) without issue. The field FileName is a varchar(200) datatype. Removing or changing the order by does not make any difference. It still doesn't allow it to run

The filenames have a naming convention of

A20120224_ABC_DEF.txt
B20120225_XYZ_ABC.txt
C20120225_XYZ.txt
A20120225_ABX.txt

FYI

This is an old system that was built using sql server 2008r2 and has been in use for several years, the issue has only just cropped up very recently. Date wasn't a datatype when it was built and I believe datetime type 20 is being used because this data is being inserted into another datetime field.

Can anyone shed some light on the reason for this?

Thanks
Noelle

EDIT

From the answers and comments below and further research I have come to to the following understanding:

Adding the field being used in the where clause alters the execution path of the select statement, in that it excludes the null values before it tries to cast them, where as excluding it from the select doesn't invoke the where clause until after it tries to cast them.

Would this be correct?

Best Answer

I have checked the data and it is all correct and can be converted to a datetime without issue.

Have you checked this for all the data in the tables or just the rows that the query will return? In any case what did you do to check this?

I suspect there is data in there, that is not pulled out by the query after all filters are applied, that does not convert as desired. Including the column directly in the outputs may make the query planner go a different way around, perhaps performing the sort required to enforce the ORDER BY before applying the filtering clauses (where otherwise it filters first taking out the rows that cause problems). Posting query plans for each case would be helpful to confirm/disprove this.

NOTE: if there is data where characters 2-to-9 are not a valid date you may have to check for this (with TRY_CONVERT) in the ORDER BY clause as you can't guarantee the order the query planner won't go the other way around at some point in the future anyway. In fact TRY_CONVERT would work directly in place of CONVERT here, as it returns NULL if the value doesn't convert so the order would not error.

Are you sure that 20 is the format you want to give to CONVERT. This is YYYY-MM-DD where your data is in YYYYMMDD format so 112 would be correct. I don't think this would cause the error you are seeing though as the conversion works in some cases.

Related Question