Conditional where statement: select different time period when query date varies

Question

I have a query need to select different time period. The logic is:

if today is Tuesday, select Saturday 00:00:00 ~ Monday 23:99;
if today is other weekdays, select the previous working day (00:00:00 ~ 23:99).

Here is the query I have:

Select *
From ...
WHERE 
    (DATENAME(DW,GETDATE())= 'Tuesday'
    AND (<@SalesDate> BETWEEN DATEADD(DAY, -3, GETDATE()) and DATEADD(DAY, -1, GETDATE())))
OR
    (DATENAME(DW,GETDATE())<> 'Tuesday'
    AND (<@SalesDate> BETWEEN DATEADD(DAY, -1, GETDATE())and DATEADD(DAY, -1, GETDATE())))

It didn't return the result that I want. Anyone can help me? thank you.

Hi @Barmar, it is Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64) Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 — QestEver2015, Dec 12 '14 at 09:55

score 0 · Accepted Answer · edited May 23 '17 at 11:50

BETWEEN is a bit finicky when it comes to date datatypes. See this question.

GETDATE() will return the time for today. You need to strips it off.

-- Today's date, stripped of time
DECLARE @Today datetime = CONVERT(datetime,CONVERT(date,GETDATE()))

Select *
From ...
WHERE 
    (DATENAME(DW,GETDATE())= 'Tuesday'
    AND (@SalesDate >= DATEADD(DAY, -3, @Today) and @SalesDate < DATEADD(DAY, -1, @Today)))
OR
    (DATENAME(DW,GETDATE())<> 'Tuesday'
    AND (@SalesDate >= DATEADD(DAY, -1, @Today) and @SalesDate < DATEADD(DAY, -1, @Today)))

Conditional where statement: select different time period when query date varies

1 Answers1