tsql day of week

Question

I've been asked for an T-SQL challenge, I need to get the equivalent day on next month based on day position like this:

Today is 18/07/2011 in other words is the third Monday of this month...

Now I need to get the third Monday of next month (15/08/2011) on a SQL Server query.

This is like google calendar's recurrence rules.

I've been trying a lot of formulas but it gets very complicated, any help would be really appreciated.

Answer 1

This syntak will calculate the same day next week and put in null if it doesn't exists

declare @t datetime

set @t = '2008-01-29'

select case (datepart(day, @t)+ 6) / 7
           when (datepart(day, @t + 28)+ 6) / 7 then @t + 28
           when (datepart(day, @t + 35)+ 6) / 7 then @t + 35
           else null end

Answer 2

CREATE FUNCTION fnSameDayOfWeekNextMonth (@Date datetime)
RETURNS datetime
AS BEGIN
  RETURN (
    SELECT
      DATEADD(WEEK,
              CASE (MONTH(ApproxDate) - MONTH(@Date) + 12) % 12
                WHEN 2 THEN -1
                ELSE ThisDayNum - (DAY(ApproxDate) - 1) / 7
              END,
              ApproxDate)
    FROM (
      SELECT
        ThisDayNum = (DAY(@Date) - 1) / 7,
        ApproxDate = DATEADD(WEEK, 5, @Date)
    ) s
  )
END

This function implements the following logic:

1. Get an approximate date as the given date plus exactly 5 weeks.

2. Get the number of the day of the week in the given date's month.

3. If the month of the approximated date is 2 months after the given date's month, subtract one week from the approximated date and return the result.

4. Otherwise get the same as #2, but for the approximated date (instead of the given date).

5. Get the difference between #2 and #4.

6. Subtract #5, as the number of weeks, from the approximated date and return the result.

The Step #3 means that if the current date is a fifth something, then the resulting date will be the fourth same weekday of the following month, because the fifth one will be impossible. Update: @t-clausen.dk has offered, in my opinion, a better idea of returning NULLs in such cases. The above function can be easily modified to follow the same convention: the …WHEN 2 THEN -1… part should simply be changed to …WHEN 2 THEN NULL….