CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Be Kind To New Users
Be Kind To New Users

Reputation: 10063

Conversion from varchar to datetime resulted in out-of-range value

Note: this is not a simple problem; it is complicated by the conversion being in a view.

I am getting the following error on a select statement that is querying a couple layers of views.

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

Normally this would be a very simple problem to solve by finding the errant data and fixing it, or putting some additional logic in the select. That has not worked.

Thru some sleuthing I have determined that this is the code that is causing the error:

  ,case when (   (hm.Status >= 100 and hm.Status < 200) 
              or (hm.Status >= 300 and hm.Status < 400 and hm.StatusEff > dateadd(day, -30, GETDATE()))
              )  
         and coalesce(mb.isMortBilled, 0) = 0
         and hm.recurring = 1
        then '1' else '0' 
   end as isRecurable

So I suspected that StatusEff had garbage in it, so I add a check that it is a date that would presumably keep the later conversion from running when it did not contain a date:

  ,case when (   (hm.Status >= 100 and hm.Status < 200) 
              or (hm.Status >= 300 and hm.Status < 400 and ISDATE(hm.Statuseff) = 1 and convert(datetime, hm.StatusEff) > dateadd(day, -30, GETDATE()))
              )  
         and coalesce(mb.isMortBilled, 0) = 0
         and hm.recurring = 1
        then '1' else '0' 
   end as isRecurable

and I still get the error.

If I run the view by itself it runs fine.

I suspect that when the optimizer has to deal with a couple layers of code it screws up and attempts the conversion on hm.StatusEff without enforcing the evaluation order and protection of checking ISDATE().

This is an old version of sql server that we are working on updating; but I can't update now.

I am thinking there may be some way to use a CASE statement to more explicitly force the order of the evaluation.

Another thought is: Does SQL have the concept of evaluation order?

[EDIT] to clarify: the hm.StatusEff varchar always contains a date that can be converted to a datetime when the status is in the indicated range. Be design it will contain other than a date when status is outside of that range. I believe the problem is that the conversion is being attempted on that varchar without the protection of checking hm.Status first. This seems to violate the order of precedence for operators.

[EDIT] Using @Used_by_Already 's suggestion I did this and it worked:

  ,case when hm.Status >= 100
         and hm.Status < 200 
         and coalesce(mb.isMortBilled, 0) = 0
         and hm.recurring = 1
        then '1' 
        when ISDATE(hm.Statuseff) = 0 -- force this to happen before convert(datetime) below.
        then '0'
        when hm.Status >= 300 and hm.Status < 400 and convert(datetime, hm.StatusEff) > dateadd(day, -30, GETDATE())
         and coalesce(mb.isMortBilled, 0) = 0
         and hm.recurring = 1
        then '1' 
        else '0' 
   end as isRecurable

Upvotes: 0

Views: 1323

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35573

You can dictate what happens if that column is not a date by using a preceding when e.g.

  ,case when ISDATE(hm.StatusEff) = 0 then '0'
        when (   (hm.Status >= 100 and hm.Status < 200) 
              or (hm.Status >= 300 and hm.Status < 400 and convert(datetime, hm.StatusEff) > dateadd(day, -30, GETDATE()))
              )  
         and coalesce(mb.isMortBilled, 0) = 0
         and hm.recurring = 1
        then '1' else '0' 
   end as isRecurable

This should reduce the incidence of the conversion failure, but if that column is a varchar that attempts to store dates strings then there will always be potential issues. It would be much better to have that column as a true temporal data type.

Also note that this is an "implicit conversion" because a string is being forced into a datetime for the comparison to the dateadd() function. Implicit conversions should be avoided in my opinion.

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

In the WHERE the engine can and will commute ANDed predicates as it "wants" (if it looks more promising in regard to optimization). But I don't know if holds true for a CASE too.

But order of execution is not necessarily the cause for this.

From the manual ISDATE (Transact-SQL):

Note that the range for datetime data is 1753-01-01 through 9999-12-31, while the range for date data is 0001-01-01 through 9999-12-31.

For example if you have a '0001-01-01' in there, isdate() accepts it, it's a valid date. But the conversion to a datetime will fail. So you should also check if hm.statuseff is after or at 1753-01-01.

isdate(hm.statuseff) = 1
AND convert(date, hm.statuseff) >= convert(date, '1753-01-01')
AND convert(datetime, hm.statuseff) > dateadd(day, -30, getdate())

Best of course would be to correct the data and a proper data type for that column.

Upvotes: 1

Related Questions