Using case statements with IsDate in SQL where clause

Question

I am trying to clear the where where 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 used to use a conversion error inside the where clause. I fixed the error by following the tips on this here , but I feel that there should be an easier way to achieve the same result.

Answer 1

You need to perform the conversion in the case . SQL is a descriptive language and does not guarantee processing order. Thus, the where clause does not necessarily occur before another processing, even if it is in a subquery or CTE. However, SQL guarantees the processing order in the case (which does not contain 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 conversion to TimeStamp for valid values. Then you can use the variable in the outer query.

I also urge you to get used to the standard ANSI date formats (YYYYMMDD or YYYY-MM-DD) instead of ambiguous formats such as '11 / 10/2012 '. It may be clear to you that this means 2012-11-10., Or it is 2012-10-11., But the format is ambiguous.

Answer 2

I like CTE for this, or you can also do temp tables, variable tables, or an embedded view such as @Derek.

Basically, we will first take the correct data type and then create our query much easier:

 ;with CTE as ( -- Bring back the column as datetime select case when isdate(UTC_Time_Stamp) = 1 then cast(UTC_Time_Stamp as datetime) end as UTC_Time_Stamp from [Table] ) -- Simple select with the proper datatype select convert(varchar(50), UTC_Time_Stamp, 127) as [TimeStamp] from CTE -- May still need gt and lt functionality where UTC_Time_Stamp between cast('11/09/2012' as datetime) and cast('11/10/2012' as datetime) 

It seems that you use arbitrarily small and large values ​​for TimeStamp when you have non-dates, which is probably not necessary when comparing, so I deleted them.

Note that I use the datetime data type in CTE and for comparison, only converting it to a string for presentation.

Also note that between included, so you may need to return to a separate sentence > and < where.

Answer 3

It's easier to do something like this (using any converting / between sentence in a predicate that suits you):

 SELECT CONVERT(datetime, [UTC_Time_Stamp], 127) as TimeStamp FROM ( select [UTC_Time_Stamp] from Table WHERE ISDATE([UTC_Time_Stamp]) = 1 ) a WHERE convert(datetime, [UTC_Time_Stamp], 127) between '11/9/2012' and '11/10/2012' ORDER BY TimeStamp;