Using case statements with IsDate in a SQL where clause

sql, sql-server-2008-r2, t-sql

Solution

You need to do the conversion within a `case` statement. SQL is a descriptive language and does not guarantee the order of processing. So, a `where` clause does not necessarily happen before other processing, even when it is in a subquery or a CTE. However, SQL does guarantee the order of processing in a `case` statement (that contains no expressions with aggregation functions).

You can simplify your statement by using a subquery:

select TimeStamp
FROM (select t.*,
             Case When ISDATE([UTC_Time_Stamp]) = 1 Then CONVERT(datetime, UTC_Time_Stamp, 127) end) as TimeStamp
      from Table t
     ) t
WHERE coalesce(TimeStamp, cast('1/1/1900' as datetime)) > cast('11/09/2012' as datetime) and
      coalesce(TimeStamp, cast('1/1/3000' as datetime)) < cast('11/10/2012' as datetime) 
ORDER BY TimeStamp;

This does the conversion to TimeStamp, for valid values. You can then use the variable in the outer query.

I would also encourage you to get used to the ANSI standard format for dates (YYYYMMDD or YYYY-MM-DD) instead of ambiguous formats like '11/10/2012'. It may be clear to you that this means 2012-11-10 . . . or is that 2012-10-11 . . . but the format is ambiguous.

Problem

I am trying to clean up the where clause statement in the following code: ``` SELECT CONVERT(datetime, [UTC_Time_Stamp], 127) AS TimeStamp FROM Table WHERE CASE WHEN ISDATE([UTC_Time_Stamp]) = 1 THEN CONVERT(datetime, [UTC_Time_Stamp], 127) ELSE CAST('1/1/1900' AS datetime) END > CAST('11/09/2012' AS datetime) AND CASE WHEN ISDATE([UTC_Time_Stamp]) = 1 THEN CONVERT(datetime, [UTC_Time_Stamp], 127) ELSE CAST('1/1/3000' AS datetime) END < CAST('11/10/2012' as datetime) ORDER BY TimeStamp; ``` `UTC_Time_Stamp` is stored as a string and is sometimes null. I was previously running into a conversion error inside my where clause. I fixed the error following advice from this question here, but I feel like there has to be a simpler way to achieve the same result.

Original source