CODEWITHSUNDEEP
sql-servert-sql
Karan Jit Singh
Karan Jit Singh

Reputation: 605

Getting conversion failed error even when CONVERT function is not being called SQL

I use this command to select all the specific dates if the given variable is date, if it is not it should return all of the fields.

The commands works when @query is in the form of a date, but it returns an error:

"Conversion failed when converting date and/or time from character string."

when it is any other arbitrary string.

Code:

select * from table where

format(dateofbirth,'dd/MMM/yyyy') = Case 
    when ISDATE(@query)=1 then 
        format(CONVERT(datetime,@query),'dd/MMM/yyyy')  
    else
        format(dateofbirth,'dd/MMM/yyyy')

Edit:

@query can be any string for eg. "1/1/2013" , "random" , "3".

The command should return all fields if @query is not in form of a date.

Upvotes: 1

Views: 652

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726849

You can work around this problem by re-formulating your query condition like this:

declare @query as varchar(20)='blah'
SELECT *
FROM testtable
WHERE ISDATE(@query) = 0
   OR CONVERT(date, dateofbirth) = CASE ISDATE(@query)
       WHEN 1 THEN CONVERT(date, @query) ELSE NULL
   END

Demo on sqlfiddle.

The problem is that logic operators are not short-circuited in SQL, so the optimizer treats CONVERT(date, @query) as something that it can pre-compute to speed up the query. By expanding the condition to a CASE that depends entirely on @query you can eliminate the execution of the CONVERT branch when ISDATE(@query) returns "false".

Upvotes: 1

Related Questions