CODEWITHSUNDEEP
t-sqlsql-server-2012
CodeRedick
CodeRedick

Reputation: 7415

Out-of-range value when trying to filter on converted datetime value

I'm trying to pull data for certain dates out of a staging table where the offshore developers imported everything in the file, so I need to filter out the "non-data" rows and convert the remaining strings to datetime.

Which should be simple enough but... I keep getting this error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've taken the query and pulled it apart, made sure there are no invalid strings left and even tried a few different configurations of the query. Here's what I've got now:

SELECT *
FROM 
(
    select cdt = CAST(cmplt_date as DateTime), *
    from stage_hist

    WHERE cmplt_date NOT LIKE '(%'
    AND ltrim(rtrim(cmplt_date)) NOT LIKE ''
    AND cmplt_date NOT LIKE '--%'
) f
WHERE f.cdt BETWEEN '2017-09-01' AND '2017-10-01'

To make sure the conversion is working at least, I can run the inner query and the cast actually works for all rows. I get a valid data set for the rows and no errors, so the actual cast is working.

The BETWEEN statement must be throwing the error then, right? But I've casted both strings I use for that successfully, and even taken a value out of the table and did a test query using it which also works succesfully:

select 1
WHERE CAST('      2017-09-26' as DateTime) BETWEEN '2017-09-01' AND '2017-10-01'

So if all the casts work individually, how come I'm getting an out-of-range error when running the real query?

Upvotes: 1

Views: 152

Answers (1)

gotqn
gotqn

Reputation: 43646

I am guessing that this is due to the fact that in your cmplt_date field there are values which are not valid dates. Yes, I know you are filtering them using a WHERE clause, but know that Logical Processing Order of the SELECT statement is not always the actual order. What does this mean is that sometimes, the SQL Engine my start performing your CAST operation before finishing the filtering.

You are using SQL Server 2012, so you can just add TRY_CAST:

SELECT *
FROM 
(
    select cdt = TRY_CAST(cmplt_date as DateTime), *
    from stage_hist

    WHERE cmplt_date NOT LIKE '(%'
    AND ltrim(rtrim(cmplt_date)) NOT LIKE ''
    AND cmplt_date NOT LIKE '--%'
) f
WHERE f.cdt BETWEEN '2017-09-01' AND '2017-10-01'

Upvotes: 1

Related Questions