Dates rounding to nearest monday 1

[Shal2]

Hi All,

If I have two dates in a stored procedure, like

@StartDate and @EndDate, if the user puts in any dates for these two parameters, how can I round it to the nearest monday.

Let's say

user inputs:
@StartDate = '06/04/2004' and @EndDate = '06/12/2004'

I want @StartDate ='06/07/2004' and @EndDate = '06/13/2004'.

I can change it so that users can enter only start date also if needed.

Thank you,
Shal

 

[NathanGriffiths]

You could use a CASE statement like this;

SELECT @StartDate = 
CASE 
   WHEN datepart(dw, convert(datetime, @StartDate)) = 2 
      THEN @StartDate
   WHEN datepart(dw, convert(datetime, @StartDate)) between 3 and 5 
      THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @StartDate))-2), @StartDate)
   WHEN datepart(dw, convert(datetime, @StartDate)) between 6 and 7 
      THEN DateAdd(dd, 9 - datepart(dw, convert(datetime, @StartDate)), @StartDate)					
   WHEN datepart(dw, convert(datetime, @StartDate)) = 1 
      THEN DateAdd(dd, 1, @StartDate)
END

SELECT @EndDate = DateAdd(wk, 1, @StartDate)

This assumes that you have the default SET DATEFIRST setting of 7 (i.e. Sunday is the first day of the week).

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[NathanGriffiths]

I should explain that a bit more;

If the user provides a @StartDate which is a Tuesday, Wednesday or Thursday the date will be changed to that of the preceding Monday.
If @StartDate is a Friday, Saturday or Sunday the date will be changed to that of the following Monday.
If @StartDate is a Monday then obviously it is unchanged.

Hope that helps,

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[Shal2]

Thank you Nathan,

It works very well for me and thank you for the explaining it as well.

Shal