title: SQL Query - Have to determine if it is a specific day of the month (ex: 2 Tuesday of month)
body: <p>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). </p>
  
  <p>Here is the query I currently use:</p>
  
  <pre><code>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 &lt;= @nowDate
                  and isnull(enddate, DATEADD(yy, 1, @nowDate)) &gt;= @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 &lt; 24
          AND recurring_start_time_min IS NOT NULL
          AND recurring_start_time_min &lt; 60
          AND startdate &lt;= @nowDate
          AND ISNULL(enddate, DATEADD(yy, 1, @nowDate)) &gt;= @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
          )
  </code></pre>
  
  <p>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. </p>