CREATE TABLE #temp (
ID INT
,StartDate DATETIME2
,EndDate DATETIME2
)
DECLARE @Today DATE = CONVERT(DATE, GETDATE())
DECLARE @Tomorrow DATE = CONVERT(DATE, DATEADD(dd, 1, @Today))
INSERT INTO #temp (ID, StartDate, EndDate)
VALUES (1, @Today, @Tomorrow), (2, DATEADD(dd, -2, @Today), @Today), (3, '01/01/2014', '12/31/2015'), (4, @Today, NULL), (4, @Today, '12/31/2016')
SELECT *
FROM #temp
WHERE
StartDate >= @Today
AND EndDate <= @Tomorrow
OR ID = 4
AND (
StartDate >= @Today
AND EndDate IS NULL
)
Prior to this realization I thought the above query would pull back the following:
- Any records with a StartDate in the future and an EndDate in the past
- Any records with a StartDate in the future and a NULL EndDate
- Any records with an ID of 4
What I've found, though, is that the OR causes a switch to happen so that only the following are pulled back:
- Any records with a StartDate in the future and an EndDate in the past
- Any records with an ID of 4 and a StartDate in the future and a NULL EndDate
I still don't like the way it reads. I'd still rather see it written like this:
SELECT *
FROM #temp
WHERE
(
StartDate >= @Today
AND EndDate <= @Tomorrow
) OR (
ID = 4
AND StartDate >= @Today
AND EndDate IS NULL
)
But it is good to know the other way is legal.
No comments:
Post a Comment