CODEWITHSUNDEEP
sqlsql-servert-sql
Sri
Sri

Reputation: 2273

Datetime comparison between two columns with two variables

I tried to look at various online examples and couldn't get anything that relates to what I am looking for. So, I have two variables in my UDF:

DECLARE @XDATETYPE VARCHAR (20) = N'CUSTOMRENTRANGE',
        @XRENTSTART DATETIME,
        @XRENTEND DATETIME;

So if the DateType parameter is 'CUSTOMRENTRANGE' then RENTSTARTDATE AND RENTENDDATE between the @XRENTSTART and @XRENTEND.

If the DateType parameter is 'CURRENTRENTRANGE' then RENTSTARTDATE AND RENTENDDATE between the @XRENTSTART and CurrentDateTime(GetDate());

What I have tried is below is not working and give me the answer I am expecting. Is there any other way to get this sorted?

  SELECT * 
    FROM TABLE 
    WHERE (
             (@XDATETYPE = N'CUSTOMRENTRANGE'
             AND (RENTSTARTDATE >= @XRENTSTART AND RENTENDDATE <= @XRENTEND))
             OR 
             (@XDATETYPE = N'CURRENTRENTRANGE'
             AND (RENTSTARTDATE >= @XRENTSTART AND RENTENDDATE <= GETDATE()))
          )

Upvotes: 0

Views: 102

Answers (2)

shawnt00
shawnt00

Reputation: 17915

You might consider whether this reads more clearly. It might also optimize better.

SELECT * 
FROM TABLE 
WHERE RENTSTARTDATE >= @XRENTSTART
    AND RENTENDDATE <=
        CASE @XDATETYPE
            WHEN N'CUSTOMRENTRANGE'  THEN @XRENTEND
            WHEN N'CURRENTRENTRANGE' THEN GETDATE()
        END

Upvotes: 1

SMor
SMor

Reputation: 2862

Use CASE expression.

WHERE 
RENTSTARTDATE >= @XRENTSTART
AND
RENTENDDATE <= case @XDATETYPE 
    when 'CUSTOMRENTRANGE' then @XRENTEND 
                           else  GETDATE() end
order by ...;

Note I removed the difficult to read parentheses around the entire WHERE clause. That has no logical effect. Alternatively, use an IF statement to assign a variable and then simplify your query. Experiment and learn more.

Upvotes: 1

Related Questions