Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query 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?
I think you'll only get the out-of-range error if you try to convert, say a "June 31st" or a 13th month. If the text is just totally un-datelike (is that a word?) it'll give a different error.

With that inb mind, TRY_CONVERT() is a variant of CONVERT() which won't error if the format isn't quite right. You can use it to look for filenames which'll fail the convert.

Try running:

SELECT JobTasks.FileName
FROM   JobTasks 
WHERE  TRY_CONVERT(DATETIME, SUBSTRING(JobTasks.FileName, 2, 8), 20) is null