Calculating Variable Date in TSQL

[cbalian]

I am looking for a TSQL code that would calculate a specific date each month that varies based on the following condition:

I need the result that would return the date of the Thursday after the Last Wednesday of each month.

I'm guessing a code that would be (Last Wednesday of Month) + 1 Day.

My problem is I can find ways to calculate a day within a week, the last day or first day of a month, add/subtract as needed from that. But calculating the last Wednesday of a month I can't figure out how to do.

As an example the dates I would need returned at 1/31/08, 2/28/08, 3/27/08, 5/1/08, 5/29/08 ......

This is my first time here so hopefully this is in the correct place and the correct format. If not please let me know and accept my apoligies from the newbie. I appreciate any advice that may be provided.

Thanks,
Chad

[iburyak]

What I would do is to find first Thursday of the next month and then subtract 1 week from that date or 7 days which is the same.
If you need help let me know.


Good Luck.

[cbalian]

Thank you for the reply. However that would not always work. For example the first Thursday after April is May 1st, which is in fact the day I want. For most of the months it would work. But to be honest I don't know the code to figure the first Thursday or the last Wednesday.

I think I need to convert the date of the last day of the month to a day of the week (dw) and then calculate from there.

For example if the last day of the month falls on a Sunday then the Thursday would be that date -3. If the last day of the month falls on a Wednesday I would add 1 day.

the dw datepart conversion is
1=Sunday (-3)
2=Monday (-4)
3=Tuesday (-5)
4=Wednesday (+1)
5=Thursday (+/- 0)
6=Friday (-1)
7=Saturday (-2)

So I "think" I'm getting closer. But would I do an IF statement for those variables?

Thanks,
Chad

[iburyak]

Try something like this:

Declare @Date datetime, @FirstOfNextMonth datetime, @FirstWed datetime
--Set @Date = '2008-04-09 17:54:22.650'
Set @Date = getdate()
 
Select @FirstOfNextMonth = dateadd(m, 1, @Date)
 
Select @FirstOfNextMonth = convert(datetime,convert(varchar(10),year(dateadd(m, 1, @Date))) + '-' + convert(varchar(10), month(dateadd(m, 1, @Date))) + '-01 00:00')   
 
 
IF Datepart(dw, @FirstOfNextMonth) > 4  
  BEGIN  
      select @FirstWed = dateadd(ww, 1, dateadd(dd, -1 * (Datepart(dw, @FirstOfNextMonth) - 4),@FirstOfNextMonth))  
  END  
ELSE IF Datepart(dw, @FirstOfNextMonth) < 4  
    select @FirstWed =  dateadd(dd, 4 - Datepart(dw, @FirstOfNextMonth),@FirstOfNextMonth)  
ELSE IF Datepart(dw, @FirstOfNextMonth) = 4  
    select @FirstWed = @FirstOfNextMonth 
 
--Find next Thursday after last Wednesday
select dateadd(dd, -6, @FirstWed)

Good Luck.

[ck9663]

let me see if i got your requirement right. you need the date following the last wednesday of the previous month. as you said more like:

set GiveMeThisDate = LastWednesdayOfPreviousMonth() + 1

If I'm wrong, don not proceed :)

If I'm right, try this, run this query. try changing the value of the variable @mydate. test for various months.

 
declare @mydate as datetime
 
set @mydate = '06/09/2008'
 
select DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0) as FirstDayofMonth, 
 
    case 
    when datepart(dw,DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0)) between 1 and 4 then
         dateadd(dd,1,dateadd(dd,(-1 * ((datepart(dw,DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0)) ) + 3)),DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0) ))
    when datepart(dw,DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0)) between 5 and 7 then
         dateadd(dd,1,dateadd(dd,(-1 * ((datepart(dw,DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0)) ) - 4)),DATEADD(mm, DATEDIFF(mm,0,@mydate ), 0) ))
    end as DateIWant
 
 

-- CK

[cbalian]

Excellent! I tested several months and they all worked. I will build that into my stored procedure.

Thank you so much!

Chad