Avatar of rsmuckles
Flag for United States of America

asked on 

setting a weekday-dependent variable in a sql script

greetings -

I have a script which is run Mondays as part of an ETL, and I now need to run it on Sundays.  The script produces fact tables which are dependent on knowing the current date week number, and it hasn't been a problem - I just calculate the current week from getdate().  Preserving the date logic while moving the script run to Sundays is as easy as just changing every instance of getdate() to (getdate()+1).  The problem is that I occasionally need to run this script later in the week, and for reasons of business logic I need to substitute getdate() with a variable that fits these criteria:

* if the script is being run on Sunday, the date returned by the variable is the following Monday, ie. the next day
* if it's run on any other day, the date returned by the variable should be Monday of that week.  For instance, if I run the script on Wednesday, March 4, the variable should show Monday, March 2.

How can I set this variable?
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon