SQL Query - Have to determine if it is a specific day of the month (ex: 2 Tuesday of month)

Question

I have an app that allows users schedule an action to occur in the future. For example, that can select a date and schedule it to run on that day every month (ex: the 15th of each month). However, I now need to allow them to select a week day and week of the month. For example, they need to run an action the first friday of the month. Therefore I am allowing the to select the weekday (monday, tuesday, wednesday....) and week of the month (1st, 2nd, 3rd, 4th or 5th).

Here is the query I currently use:

Declare @nowString varchar(19)
        Declare @nowDateString varchar(19)
        Declare @now datetime
        Declare @lastMinute datetime
        Declare @nowDate datetime

        Set @nowString = '#currentDateTime#:00'
        Set @nowDateString = LEFT(@nowString, 10)
        set @now = @nowString
        set @nowDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @now))
        set @lastMinute = DATEADD(mi, -1, @now)

        select * 
        from message_prepared
        where schedule = '1' 
        and active = '1'
        and noaa = '0'
        and (
            (
                schedule_type = 'recurring'
                and startdate <= @nowDate
                and isnull(enddate, DATEADD(yy, 1, @nowDate)) >= @nowDate
                and (
                    @nowDateString + ' ' + isnull(recurring_start_time_hour, '00') + ':' + isnull(recurring_start_time_min, '00') + ':00' = @now
                    or @nowDateString + ' ' + isnull(recurring_start_time_hour, '00') + ':' + isnull(recurring_start_time_min, '00') + ':00' = @lastMinute
                )
                -- Test for different type of recurring
                and (
                    ( ltrim(rtrim(recurring)) = 'M' and DATEPART(dd, startdate) = DATEPART(dd, @now) )
                    or ( ltrim(rtrim(recurring)) = 'W' and DATEPART(dw, startdate) = DATEPART(dw, @now) )
                    or ltrim(rtrim(recurring)) = 'D'
                )
            )
            or (
                schedule_type = 'once'
                and startdate = @nowDate
                and (
                    @nowDateString + ' ' + onetime_start_time_hour + ':' + onetime_start_time_min + ':00' = @now
                    or @nowDateString + ' ' + onetime_start_time_hour + ':' + onetime_start_time_min + ':00' = @lastMinute
                )
            )
        )
        and repeat_multiple_times = 0

        UNION ALL

        SELECT *
        FROM MESSAGE_PREPARED
        WHERE schedule = '1' 
        AND active = 1
        AND noaa = 0
        AND recurring = 'D'
        AND repeat_multiple_times = 1
        AND startDate IS NOT NULL
        AND recurring_start_time_hour IS NOT NULL
        AND recurring_start_time_hour < 24
        AND recurring_start_time_min IS NOT NULL
        AND recurring_start_time_min < 60
        AND startdate <= @nowDate
        AND ISNULL(enddate, DATEADD(yy, 1, @nowDate)) >= @nowDate
        AND
        ( 
            CASE WHEN repeat_unit = 'M'
                THEN
                DATEDIFF(n,
                CONVERT(DATETIME, 
                CAST(DATEPART(yyyy, startDate) AS VARCHAR(4)) + '-' + 
                CAST(DATEPART(mm, startDate) AS VARCHAR(2)) + '-' + 
                CAST(DATEPART(dd, startDate) AS VARCHAR(2)) + ' ' + 
                CAST(recurring_start_time_hour AS VARCHAR(2)) + ':' +
                CAST(recurring_start_time_min AS VARCHAR(2)) + ':00', 20),
                GETDATE()) % repeat_interval
                ELSE
                DATEDIFF(n,
                CONVERT(DATETIME, 
                CAST(DATEPART(yyyy, startDate) AS VARCHAR(4)) + '-' + 
                CAST(DATEPART(mm, startDate) AS VARCHAR(2)) + '-' + 
                CAST(DATEPART(dd, startDate) AS VARCHAR(2)) + ' ' + 
                CAST(recurring_start_time_hour AS VARCHAR(2)) + ':' +
                CAST(recurring_start_time_min AS VARCHAR(2)) + ':00', 20),
                GETDATE()) % (repeat_interval * 60)
            END = 0
            OR
            CASE WHEN repeat_unit = 'M'
                THEN
                (DATEDIFF(n,
                CONVERT(DATETIME, 
                CAST(DATEPART(yyyy, startDate) AS VARCHAR(4)) + '-' + 
                CAST(DATEPART(mm, startDate) AS VARCHAR(2)) + '-' + 
                CAST(DATEPART(dd, startDate) AS VARCHAR(2)) + ' ' + 
                CAST(recurring_start_time_hour AS VARCHAR(2)) + ':' +
                CAST(recurring_start_time_min AS VARCHAR(2)) + ':00', 20),
                GETDATE()) - 1) % repeat_interval
                ELSE
                (DATEDIFF(n,
                CONVERT(DATETIME, 
                CAST(DATEPART(yyyy, startDate) AS VARCHAR(4)) + '-' + 
                CAST(DATEPART(mm, startDate) AS VARCHAR(2)) + '-' + 
                CAST(DATEPART(dd, startDate) AS VARCHAR(2)) + ' ' + 
                CAST(recurring_start_time_hour AS VARCHAR(2)) + ':' +
                CAST(recurring_start_time_min AS VARCHAR(2)) + ':00', 20),
                GETDATE()) - 1) % (repeat_interval * 60)
            END = 0
        )

This will only occur when reocurring is set to "M" and I would like to determine if today is the specific day of the week, week of the month and hour/min.

Answer 1

This is pretty simple logic. Today is the nth DOW of the month when the following is true:

1. Today is that day of the week
2. The day of the month is between 7*(n-1) + 1 and 7 * n

So, the first Monday of the month is always between the 1st and 7th, and so on. Here is an example case statement to test this:

declare @DayOfWeek varchar(255) = 'Thursday';
declare @Which int = 3;

select (case when datename(dw, Today) = @DayOfWeek and
                  (DAY(Today) - 1) / 7 = @Which - 1
             then 1
             else 0
        end)
from (select CAST(getdate() as date) as Today) t

I've structured the query this way so you can test it with different values. Just replace the expression that defines Today, with something like getdate() - 3 or '2013-01-01'.