0

I'm trying to create a flexible way to determine the last day of the previous week, knowing that this will be run in different environments that have different planning weeks (some customers use Sat-fri; Sun-Sat; Mon-Sun). I was planning on creating a variable to use as each customer's start day of the week (see below)

    Set @BeginningWeek = 1   -- 1=Mon; 2=Tues; 3=Wed; 4=Thurs; 5=Fri; 6=Sat; 7=Sun

I've been trying to use a combination of dateadd and datepart, along with the variable above to try to get the previous last day of the week, but I'm having trouble with the logic. I do not want to use Set DateFirst, these scripts will be run on a customer environment, so I do not want to change anything in their DB.

Assuming the script is run on a wednesday (9/3); and the customer's planning week is sat-fri; I would want to set another variable @EndDate = the previous friday(8/29). I need the flexibility to use the same script (changing only the @BeginningWeek value) with another customer whose planning week is Mon-Sun and set @EndDate = the previous sunday (8/31).

NRud
  • 13
  • 2

1 Answers1

0

This will get you the previous Saturday.

SELECT DATEADD(day,
               -1 - (DATEPART(dw, GETDATE()) + @@DATEFIRST - 2) % 6,
               GETDATE()
       )

You should be able to replace the %6 bit with a parameter based on what the actual desired day is.

Andrew
  • 8,445
  • 3
  • 28
  • 46