We help IT Professionals succeed at work.
Get Started

dynamic date parameters

Last Modified: 2012-06-27
Say I have a procedure that performs X, Y and Z, and returns a result-set every week, from Wednesday to Wednesday.

This retrieval will be scheduled to run every Wednesday around 10PM or so, and it must always collect the data from the previous Wednesday to the current day (also Wednesday).  For example, this week's retrieval would have been from a @start of 3/23/1011 to a @stop of 3/30/2011.  The @start should always default to last Wednesday, and @stop should always default to this Wednesday.  They must also be passable, if needed.

I'm using the post below to derive @start and @stop now, but I am not sure that it is completely reliable.  Any suggestions?

		@start DATETIME,
		@stop DATETIME,
		@weekday INT

	SET @now = GETDATE()
	SET @weekday = (DATEPART(WEEKDAY,@now) + @@DATEFIRST - 1 ) % 7  + 1
	SET @start = CASE WHEN @weekday = 4 
		   THEN CASE WHEN DATEPART(hour,@now) >= 10
	 THEN DATEADD(HOUR, 0, CONVERT(datetime, CONVERT(varchar(10),@now, 120), 120)) 
 ELSE DATEADD(HOUR, 0, CONVERT(datetime, CONVERT(varchar(10),@now-7, 120), 120)) 
	   WHEN @weekday > 4
THEN DATEADD(HOUR, 0, CONVERT(datetime, CONVERT(varchar(10),@now, 120), 120) - @weekday + 4) 
	   ELSE DATEADD(HOUR, 0, CONVERT(datetime, CONVERT(varchar(10),@now, 120), 120) - @weekday - 3) 

	SET @start = DATEADD(dd,-7,@start)
	SET @stop = DATEADD(dd,7,@start)

Open in new window

Watch Question
Expert of the Quarter 2010
Expert of the Year 2010
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE