CODEWITHSUNDEEP
sqlt-sql
StayPuft
StayPuft

Reputation: 135

Using Case in a Where Clause for Dates

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]

Upvotes: 1

Views: 83

Answers (4)

AHiggins
AHiggins

Reputation: 7227

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'
          )
         )
      )
     )

Upvotes: 1

paparazzo
paparazzo

Reputation: 45106

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

Upvotes: 1

Horaciux
Horaciux

Reputation: 6487

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

Upvotes: 2

Leonardo Hern&#225;ndez
Leonardo Hern&#225;ndez

Reputation: 191

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')
)

Upvotes: 0

Related Questions