t-sql: Flipping a BETWEEN operator depending on row value

Question

I've simplified my example here massively, so please excuse it if it looks to simple for what I'm trying to solve.

Is it possible to turn BETWEEN, into a NOT BETWEEN depending on a row value (i've used a variable here for simplicity), without duplicating the between statement?

I understand the use of case statements and boolean logic to help make static sql more "dynamic", but I can't seem to figure out a way for operators like BETWEEN.

Perhaps using some clever bitwise operation?

I have some set based SQL in development that is becoming difficult to manage already, so I'm hoping to simplify it by avoiding duplication.

I'm trying to do it in a SET based way, avoiding UDF's if possible.

DECLARE @is int
set @is = 1

DECLARE @TestTable TABLE
(
    Name varchar(100),
    DOB datetime
)

INSERT INTO @TestTable(Name, DOB)
SELECT 'bob', '2011-08-18 10:10:10.100' UNION ALL
SELECT 'fred', '2014-08-18 10:10:10.100'


SELECT Name, DOB
FROM @TestTable
WHERE
    (@is = 1) AND (DOB BETWEEN '2011-08-18' AND '2012-08-18') 
    OR
    (@is = 0) AND (DOB NOT BETWEEN '2011-08-18' AND '2012-08-18')

So in the example above, if @is = 1, then bob is returned. If @is = 0, then fred is returned.

---

EDIT: To clarify, the above statement works fine, but I'm attempting to optimise it to avoid having to write 2 BETWEEN statements. I wish to flip the logic depending on the value of @is.... somewhere using is to put in the NOT.

---

EDIT 2:

Please see below for some pseudo code for what I'm trying to achieve. It's pseudo, as t-SQL doesn't allow "NOT" to be "injected" in the code in a set based operation like this (I'm trying to avoid stringy-dynamic sql).

SELECT Name, DOB
FROM @TestTable
WHERE
(
    DOB
        CASE @is
            WHEN 0
            THEN NOT ---pseudo code, this won't actually work, but gives an idea of what I'm trying to achieve.
        END
    BETWEEN '2011-08-18' AND '2012-08-18'
) 

Answer 1

I think you just need more parens...

WHERE
    (   (@is = 1) AND (DOB BETWEEN '2011-08-18' AND '2012-08-18')   )
    OR
    (   (@is = 0) AND (DOB NOT BETWEEN '2011-08-18' AND '2012-08-18')   )

EDIT

Got it -- so then something like this:

WHERE @is = CASE WHEN DOB BETWEEN '2011-08-18' AND '2012-08-18' THEN 1 ELSE 0 END