Using Case in a Where Clause for Dates

Question

I currently have this WHERE clause that includes the following parameters.

WHERE 
    Project ID=110
    AND ((CAST(saa.Date AS DATE) >= '09/24/2014' AND CAST(saa.Date AS DATE) <= '09/24/2014') OR saa.Date IS NULL))

The tricky part here is that the saa.Date is NULL section is going to pull up ALL Null Values in all dates (which is excessive) I only want to use the following Date Range for the Null Values

(
        (CAST(sa.StartDateTime AS DATE) >= '09/24/2014' AND CAST(sa.StartDateTime AS DATE) <= '09/24/2014')
        OR
        (CAST(sa.EndDateTime AS DATE) >= '09/24/2014' AND CAST(sa.EndDateTime AS DATE) <= '09/24/2014')
)

So I'm trying to figure out how I can create a CASE statement that would work that would be something like IF saa.Date is NULL Then [Use Date Range Parameters above]

score 2 · Accepted Answer · edited May 23 '17 at 12:13

2

I'll base my answer in @AHiggins's but adding performance an readability

-- sergability

-- avoiding cast

-- using between

WHERE 
    ProjectID=110 AND 
     (
      (
        saa.Date between '09/24/2014 00:00:00.000' AND '09/24/2014 23:59:59.999'
      ) OR 
      (
        saa.Date IS NULL AND
         (
            sa.StartDateTime between '09/24/2014 00:00:00.000' AND  '09/24/2014 23:59:59.999'
         ) OR
         (
            sa.EndDateTime between  '09/24/2014 00:00:00.000' AND '09/24/2014 23:59:59.999'
         )
      )
     )

Make sure you have indexes on thos date columns

edited May 23 '17 at 12:13

Community

1
1

answered Sep 24 '14 at 15:24

Horaciux

6,322
2
22
41

A few hours ago, I received a down votes for not mention the surce which inspired my answer!!! :) – Horaciux Sep 24 '14 at 15:53
Weirdly enough this only produces the non-null values – StayPuft Sep 24 '14 at 16:15
@StayPuft could you add schema, data sample and actual query on SQLFidle? – Horaciux Sep 24 '14 at 17:10
Actually did my own fiddling around and worked! Thanks for the follow up though – StayPuft Sep 25 '14 at 13:34

AHiggins · Answer 2 · 2014-09-25T11:50:16.743

If I'm reading it right, you should be able to do this with Boolean logic and a few million parentheses:

EDIT: noticed in the comments on @horaciux's answer that there's an issue, realized we actually need a couple more parentheses. I've borrowed back the borrowed code and added the extras below.

WHERE 
    ProjectID=110 AND 
     (
      (
        saa.Date between '09/24/2014 00:00:00.000' AND '09/24/2014 23:59:59.999'
      ) OR 
      (
        saa.Date IS NULL AND
         ( -- need to wrap the next two conditions in a single set of parentheses
          (
            sa.StartDateTime between '09/24/2014 00:00:00.000' AND  '09/24/2014 23:59:59.999'
          ) OR
          (
            sa.EndDateTime between  '09/24/2014 00:00:00.000' AND '09/24/2014 23:59:59.999'
          )
         )
      )
     )

StayPuft, I think there's still one set of missing parentheses: I've borrowed back the code @horaciux improved and edited my answer. — AHiggins, Sep 25 '14 at 11:51

score 1 · Answer 3 · edited Sep 24 '14 at 15:51

1

You do know that

    >= '09/24/2014' 
AND <= '09/24/2014'

is the same as = '09/24/2014'

where ProjectID=110 
  AND CAST(saa.Date AS DATE) = '09/24/2014' 
   OR (
        saa.Date IS NULL 
        AND
            ( 
              CAST(sa.StartDateTime AS DATE) = '09/24/2014'
              OR
              CAST(sa.EndDateTime AS DATE)   = '09/24/2014' 
            )              
      )

This is more efficient than a cast:

DATEADD(dd, DATEDIFF(dd, 0, COL),0)

But the answer from Horaciux is even more efficient

edited Sep 24 '14 at 15:51

AHiggins

7,029
6
36
54

answered Sep 24 '14 at 15:29

paparazzo

44,497
23
105
176

thanks for mention that. It feels good when it came from high reputation users. – Horaciux Sep 24 '14 at 15:40

score 0 · Answer 4 · answered Sep 24 '14 at 14:06

0

Try this

(
        (CAST(ISNULL(sa.StartDateTime, '09/24/2014') AS DATE) >= '09/24/2014' AND CAST(ISNULL(saa.StartDateTime, '09/24/2014') AS DATE) <= '09/24/2014')
)

answered Sep 24 '14 at 14:06

Leonardo Hernández

191
2
12

Using Case in a Where Clause for Dates

4 Answers4