<div>
How about this....just replace @Date with GETDATE() in the query.<br />
<br />
DECLARE @Date DATETIME<br />
SELECT @Date = '3/1/2009'<br />
<br />
SELECT DATEADD(DAY,-DATEPART(DW,@<wbr />Date)+2,@D<wbr />ate) AS NewDate
</div>


<div>
congrats, Aaron, you beat me!<br />

</div>


<div>
As opposed to the second Monday of the week?<br />
<br />
:-)<br />

</div>


<div>
whoops? LOL i see you two beat me must remember to refresh, got the email alert opened question, then answered phone, then came back to question....
</div>


<div>
I don't think either of these examples will work. &nbsp;The first:<br />
<br />
DECLARE @Date DATETIME<br />
SELECT @Date = '3/1/2009'<br />
<br />
SELECT DATEADD(DAY,-DATEPART(DW,@<wbr />Date)+2,@D<wbr />ate) AS NewDate<br />
<br />
includes a hard-coded date of 3/1/2009. &nbsp;I would have to change this every week in order to make it correct. &nbsp;I'm looking for a way of saying:<br />
<br />
&nbsp;'if today is (any) Sunday, set the variable to the date for tomorrow, which is Monday. &nbsp;if today is not Sunday, then set the variable to the date for the Monday of this week.'<br />
<br />
also, if I were to run the second and third recommendations:<br />
<br />
select dateadd(day,-1*(datepart(w<wbr />eekday, getdate())-2),getdate())<br />
<br />
on Sunday, March 1, won't it return Monday, Feb 23? &nbsp;It's my understanding that weeks run Monday-Sunday in SQL Server 2005. &nbsp;I would need it to return the Monday of the next week if it's run on a Sunday, otherwise it should return the Monday of the current week.
</div>


<div>
Also, by default, SQL Server considers a week to be Sunday - Saturday.
</div>


<div>
substituting Sunday, March 1 for getdate() ... I still get March 2<br />
<br />

<pre><code class="code-1 nolinks" id="code-265122"><span>select dateadd(day,-1*(datepart(weekday, getdate())-2),getdate())</span>
<span> </span>
<span>select dateadd(day,-1*(datepart(weekday, '2009-03-01')-2),'2009-03-01')</span>
</code></pre>
</div>


<div>
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek<br />
<br />

</div>


<div>
rsmuckles,<br />
<br />
All 3 solutions provided by me, DanielWilson, and cs97jjm3 are all the same and will produce the exact same result. &nbsp;Here's an example of what date will be returned:<br />
<br />
2/28/09 (Sat) - 2/23/09 (Mon)<br />
3/1/09 (Sun) - 3/2/09 (Mon)<br />
3/2/09 (Mon) - 3/2/09 (Mon)<br />
3/3/09 (Tue) - 3/2/09 (Mon)<br />
3/4/09 (Wed) - 3/2/09 (Mon)<br />
3/5/09 (Thu) - 3/2/09 (Mon)<br />
3/6/09 (Fri) - 3/2/09 (Mon)<br />
3/7/09 (Sat) - 3/2/09 (Mon)<br />
3/8/09 (Sun) - 3/9/09 (Mon)
</div>


<div>
you're right, I made an error in testing it, thanks to all of you
</div>


<div>
you're right, this will work for me . . . thanks<br />
<br />
&nbsp;- J
</div>


<div>
<div class="content wysiwyg-content">
greetings - <br />
<br />
I have a script which is run Mondays as part of an ETL, and I now need to run it on Sundays. &nbsp;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(). &nbsp;Preserving the date logic while moving the script run to Sundays is as easy as just changing every instance of getdate() to (getdate()+1). &nbsp;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:<br />
<br />
* if the script is being run on Sunday, the date returned by the variable is the following Monday, ie. the next day<br />
* if it's run on any other day, the date returned by the variable should be Monday of that week. &nbsp;For instance, if I run the script on Wednesday, March 4, the variable should show Monday, March 2.<br />
<br />
How can I set this variable?
</div>
</div>


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?

setting a weekday-dependent variable in a sql script

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.