CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user42995
user42995

Reputation: 355

Simple SELECT statement to get last Wednesday of any month

I have one table which is populated with data every Wednesday. So I need to fetch those data from last Wednesday to recently occurred Wednesday.

For Example: If today is Thursday (06/06/2013) then I need to fetch data from 29/05/2013 (i.e Last Wednesday of previous month, May) to 05/06/2013 (Yesterday)

I have tried

select dateadd(dd,datediff(dd,0,'GetDate()')/7 * 7 + 2,0) which gives 05/06/2013 select dateadd(wk,-1,datediff(dd,0,'GetDate()')/7 * 7 + 2) which gives 29/05/2013

Here in the code, dateadd(dd,datediff(dd,0,'GetDate()')/7 * 7 goes to 1st day of the week (here in this case it is Monday) and by adding +2 it gives Wednesday's date.

Problem The code works fine except for Mondays.

For example: consider Next Monday (i.e 10/06/2013),

It gives the result from 05/06/2013 (Yesterday) to 12/06/2013 (Next Wednesday)
Instead I need 29/05/2013 (i.e Previous of Last week Wednesday) to 05/06/2013 (Yesterday)

Any Ideas?

Upvotes: 1

Views: 4112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

DECLARE @ActualDatePayable DATETIME,@LASTDateMonth DATETIME

SET @ActualDatePayable='02/01/2013'

SET @LASTDateMonth=DATEADD(d,-1,DATEADD(m,1,@ActualDatePayable))

SET @ActualDatePayable=(select MAX(date) AS LastWednesday from (

select dateadd(day,number,@ActualDatePayable) as date from master..spt_values 
where type='p' and number < DAY(@LASTDateMonth) ) t
where   datename(weekday,date) = 'Wednesday')

SELECT @ActualDatePayable

Upvotes: 4

Related Questions