Select between two dynamic (variable) dates SQL

Question

I have a query that runs once a day that I would like to share however I need to remove the part where the other users in my team will have to edit it. Essentially, it's run Monday thru Friday. I want, if today is Monday, give me the last 3 days worth of data. Any other day, just give me yesterday's data.

So far this is what I have:

Update: They are all strings, so now I get the following error.

"Incorrect syntax near the keyword 'BETWEEN'."

DECLARE @daychecker varchar(max) = FORMAT(GETDATE(), 'dddd')
DECLARE @daterange0 varchar(max)
DECLARE @daterange1 varchar(max) = FORMAT(GETDATE()-3, 'yyyy-MM-dd')
DECLARE @daterange2 varchar(max) = FORMAT(GETDATE()-1, 'yyyy-MM-dd')
    IF  @daychecker = 'Wednesday'
        BEGIN
        SET @daterange0 = BETWEEN @daterange1 AND @daterange2
    END
    ELSE
        BEGIN
        SET @daterange0 = FORMAT(GETDATE()-1, 'yyyy-MM-dd')
    END
SELECT @daterange0;

The result for today as an example should return yesterday's date. But that doesn't work. I will consider all options including hardcoding some sort of master start date that we can count from like maybe the start of the year or something.

SET @daterange0 = BETWEEN @daterange1 AND @daterange2 is not valid syntax — Alan Burstein
– Alan Burstein, Commented Sep 11, 2019 at 20:01

Robert Sheahan · Accepted Answer · 2019-09-11 17:05:45Z

1

You're much better off defining 2 dates, a start date and end date, and filtering your query based on them. EDIT: I'm now unsure if you want actual dates for filtering data, or a label for a report. I modified my answer to include the latter, use whichever you want and ignore the other ...

DECLARE @DateStart DATE 
DECLARE @DateEnd DATE 
DECLARE @LableRange varchar(max)
SELECT DATEPART(WEEKDAY, GETDATE()) --Sun=1, Mon=2, ...
IF DATEPART(WEEKDAY, GETDATE()) = 2 BEGIN
    SET @DateStart = DATEADD (DAY, -5,GETDATE())
    SET @DateEnd = DATEADD (DAY, -2,GETDATE())
    SET @LableRange = CONCAT(FORMAT(@DateStart, 'yyyy-MM-dd'), ', '
      , FORMAT(DATEADD(day,1,@DateStart), 'yyyy-MM-dd'), ', '
      , FORMAT(DATEADD(day,2,@DateStart), 'yyyy-MM-dd'))
   -- or maybe this format is better
   --SET @LableRange = CONCAT('BETWEEN '
      --, FORMAT(@DateStart, 'yyyy-MM-dd'), ' AND '
      --, FORMAT(DATEADD(day,2,@DateStart), 'yyyy-MM-dd'))
END ELSE BEGIN
    SET @DateStart = DATEADD (DAY, -1,GETDATE())
    SET @DateEnd = GETDATE()
    SET @LableRange = FORMAT(@DateStart, 'yyyy-MM-dd')
END

SELECT @LableRange

SELECT * FROM SomeTable as T
WHERE T.TestDate < @DateEnd AND T.TestDate >= @DateStart

Note that this works even if the date you are filtering on is a datetime instead of pure date.

edited Sep 11, 2019 at 17:05

answered Sep 11, 2019 at 16:48

Robert Sheahan

2,0851 gold badge11 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Neville Kuyt Over a year ago

This makes a lot of sense, except that the start date of the week is a config value, so you need to make sure you check for that

Robert Sheahan Over a year ago

True, thanks Neville! Yes, the setting is unlikely to change, but it might be set to have the week start on Monday, in which case my test should be = 1 instead of = 2.

HABO Over a year ago

Tip: ( @@DateFirst + DatePart( weekday, GetDate() ) - 1 ) % 7 + 1 will always return an integer from 1 to 7 with 1 corresponding to Sunday regardless of the setting of DateFirst or Language.

Rookie Over a year ago

Yeah I ran it and simply returned the #4 (which is wednesday). I need it to return 3 values when the first condition is satisfied. That is, in the code snippet above, it should give me 1,2,3 (sun, mon, tue).

Robert Sheahan Over a year ago

OK, I modified it to include a label describing the date, maybe that label was all you wanted? If so, you can drop the @DateEnd variable

Collectives™ on Stack Overflow

Select between two dynamic (variable) dates SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related