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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

Given that you've tagged the question as being 2008 R2, presumably TRY_CONVERT() won't work for you.

This is a problem that the company I work for frequently have with data imports. What we've found is that even if your WHERE clause is set up so that only "valid" values should be returned, that you can still get the error if any row in the table would be invalid for the conversion.

The solution that's worked for me in these scenarios is to move the data into a temporary table without converting (but including conditions in the WHERE clause that helped assure that the data pulled fits the conditions). Then, select the resulting temp table data with the conversion after that.

Answer 4

I'm not sure if this will work for your scenario, but you 'might' try using the IsDate function to verify your data. The last two rows in my sample data are invalid date strings and return NULL due to the CASE expression.

DECLARE @T TABLE (Filename VARCHAR(200))

INSERT INTO @T (Filename)
VALUES ('A20120224_ABC_DEF.txt')
    ,('B20120225_XYZ_ABC.txt')
    ,('C20120225_XYZ.txt')
    ,('A20120225_ABX.txt ')
    ,('A20120230_ABX.txt ') --invalid date
    ,('A2012022_ABX.txt ') --invalid date

SELECT CASE 
        WHEN isdate(SUBSTRING(Filename, 2, 8)) = 1
            THEN CONVERT(DATETIME, SUBSTRING(Filename, 2, 8), 20)
        ELSE NULL
        END
FROM @T
ORDER BY CASE 
        WHEN isdate(SUBSTRING(Filename, 2, 8)) = 1
            THEN CONVERT(DATETIME, SUBSTRING(Filename, 2, 8), 20)
        ELSE NULL
        END

Results

| DateField     |           |
|---------------|-----------|
| NULL          |           |
| NULL          |           |
| 2012-02-24 00 | 00.00.000 |
| 2012-02-25 00 | 00.00.000 |
| 2012-02-25 00 | 00.00.000 |
| 2012-02-25 00 | 00.00.000 |