0

I get this error when I run the following code:

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

Declare @t date
Set @t = DATEADD(DAY, -75, GETDATE())

Select * from
(
SELECT changeField,  CONVERT(date, newValue) newDate, changeDateTime, pubId, changeType, @t today
      FROM ChangeLog
      WHERE ISDATE(newValue) = 1 
) dates
where newDate < today

If I comment out where newDate < today, it works. There should not be any conversion in the where clause.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
Ty Petrice
  • 1,769
  • 1
  • 13
  • 7
  • 4
    The `CONVERT(date, newValue)` can happen before rows are filtered out by the `WHERE` clause. You can use `TRY_CONVERT` if 2012+ or else need to do `CONVERT(date, CASE WHEN ISDATE(newValue) = 1 THEN newValue END)` – Martin Smith Aug 02 '13 at 17:43
  • CONVERT(date, CASE WHEN ISDATE(newValue) = 1 THEN newValue END) solved the problem. It was evaluating the outer where clause before the subquery. – Ty Petrice Aug 02 '13 at 18:08

0 Answers0