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