The 18th. So, okay, that's not a very interesting question in and of itself. A better question might be "what's the Nth occurrence of the Mth day in each month?" Like, what are all of the third Thursdays in 2017?
The literal -- and rather boring -- answer is January 19, February 16, March 16, April 20, May 18, June 15, July 20, August 17, September 21, October 19, November 16, December 21.
So what's the interesting question here? How can one compute these using a T-SQL query. If you are using SQL Server 2005, that's not too hard as this Table Valued Function demonstrates:
<![CDATA[create function dbo.NthGivenWeekDaysForYear(@year smallint,@gap tinyint,@dayOfWeek tinyint)
returns @d table(date datetime) as begin
-- fill up a table variable for the days in the requested year
declare @days table(date datetime,dayOfWeek tinyint,Month tinyint);
declare @ds datetime, @de datetime;
set @ds = cast(cast(@year as varchar(4)) + '-01-01' as datetime)
set @de = dateadd(yyyy,1,@ds);
while(@ds LESS-THAN @de)
begin
insert into @days values (@ds,datepart(dw,@ds),datepart(MM,@ds));
set @ds = dateadd(dd,1,@ds);
end;
-- This CTE computes the occurrence number for each weekday within the given
-- month (e.g., first Monday gets a seq of 1, second Monday is 2...) Resets
-- with the month changes. Filters down to just the desired date.
with c as (select date,row_number() over (partition by month order by date) as seq from @days where dayOfWeek=@dayOfWeek)
-- We can now insert the target dates. We know that seq has the
-- nth occurrence of that weekday in the month.
insert into @d select date from c where seq = @gap;
return;
end;
]]>
The magic here is in the row_number() function and in its over clause. Here the row_number() is computed as a sequentially increasing value for each occurrence of a given weekday within a month. We sort by date here so the first day of interest gets the value of one in the sequence. The partition by reset the sequence to one when the month changes.
So why would we want to write this as a TVF rather than say a stored procedure? The immediate answer might not be so obvious, but here's a use case: suppose your company has started a bonus program for newly hired employees. Employees are eligible to participate in the program so long as they were hired before the third Friday in the month when payroll data get generated. All it takes to refractor this function is a couple of tweaks. First, we need to have the function accept a hiredate as a parameter. Second, we need to modify the final insert statement to this:
<![CDATA[insert into @d select date from c where seq = @gap and datepart(m,date) = datepart(m,@hireDate) and datepart(d,@hireDate) LESS-THAN-OR-EQUAL-TO datepart(d,date)
]]>
We can now leverage the TVF using the new APPLY operator, in this case, the OUTER APPLY variant thusly:
<![CDATA[select EmployeeID,hireDate,date from adventureworks.HumanResources.Employee e outer apply dbo.NthGivenWeekDaysForYear(2002,3,6,e.hiredate) where datepart(yyyy,e.hiredate) = 2002
]]>
Because Employee 284 missed the cutoff by one day, the TVF returns a null value for that row, and we know that this employee didn't qualify for any bonus for May of 2002.