Find the Nth Day-of-the-week in a Month

Last week a reader e-mailed and asked if I had a UDF that could tell if there were 4 or 5 of any of the seven days of the week in a month. I didn't.

But I did have this week's UDF, udf_DT_NthDayInMon, which returns the date of the Nth day of the week in a month. That is, the 3rd Tuesday or the 5th Monday. That is of course, if there is a 5th Monday in the month. This month has 5 Mondays, Tuesdays, and Wednesdays but only 4 of the other days of the week.

I used udf_DT_NthDayInMon as the basis for writing a UDF for the reader that answered his question. I'll show it to you in a couple of weeks but for this week we have udf_DT_NthDayInMon, which I consider more useful. Here's the CREATE FUNCTION script:

/------- Start copying below this line -\

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE   FUNCTION dbo.udf_DT_NthDayInMon (

    @nYear as int           -- 4 place year, ie 2002
  , @nMonth as int          -- Month Number 1 to 12
  , @nthOccurance as int    -- Which one 1st 2nd 3rd 4th or 5th
  , @sDayName as varchar(9) -- Day of the week
       -- Sun, Mon, ... Sun or Sunday Monday, ..., Sunday
)  RETURNS datetime -- Nth @sDayname occurs on this date
   WITH SCHEMABINDING 
/*
* Returns a datetime For the Nth occurrence of a Day in the
* month. Such as the 3rd Tuesday.  Returns NULL if the date 
* doesn't exist, such as the 5th Monday in June, 2002.
* Sensitive to setting of DATEFIRST. Assumes default value of 7.
*
* Example:
select 'This year US Presidents day is on ' + Convert(varchar(12),
          dbo.udf_DT_NthDayInMon(year(getdate()), 2, 3, 'Mon' ))
*
* Test:
select 'Test 1 ' + case when '2003-02-17'
      = dbo.udf_DT_NthDayInMon(2003, 2, 3, 'Mon' )
                 then 'Worked' else 'ERROR' end
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of the Week  Vol 2 #14 3/23/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @OnDate datetime -- working date.
   , @dtFirstOfMonth datetime -- 1st of the month  
   , @nFirstIsOnADW INT -- Day of week for the first (1=SUN)
   , @nDWofDayName int -- translate sDayNum to a WeekDate
   , @nDays2Add2GetToFirstDay int -- how many days do we need to
             --  add to get to the first occurance of @sDayname
   , @dtFirstOccurs datetime -- 1st @sDayName in the month

-- There can only be 5 of any day in a month, else return null
	IF @nthOccurance > 5 RETURN NULL

	-- Clean the input, Use UPPER incase on a case sensitive server
	SELECT @sDayName = LEFT(UPPER(LTRIM(RTRIM(@sDayName))),3)
	     , @dtFirstOfMonth = CONVERT (SMALLDATETIME
                                    , CONVERT(CHAR(4), @nYear) 
                             + '-' + CONVERT(VARCHAR(2), @nMonth)
                             + '-' + CONVERT(VARCHAR(2), 1)
                                , 110 -- required for determinism
                               )

    -- Compute the DOW for the first of the month 1=Sun, 
    -- in a way that doesn't use DATEPART, and mantains
    -- Determinism (that's why the explicit conversion)
	SELECT @nFirstIsOnAdw = 
         1 + (datediff (d
                      , Convert(datetime, '1899-12-31', 120)
                      , @dtFirstOfMonth
                        ) 
               % 7
              )

	select @nDWofDayName =  CASE @sDayName
			WHEN 'SUN' then 1 WHEN 'SUNDAY'  THEN 1
			WHEN 'MON' THEN 2 WHEN 'MONDAY'   THEN 2
			WHEN 'TUE' THEN 3 WHEN 'THURSDAY' THEN 3
			WHEN 'WED' THEN 4 WHEN 'WEDNESDAY' THEN 4
			WHEN 'THU' THEN 5 WHEN 'THURSDAY' THEN 5
                                 WHEN 'THR' THEN 5 -- Alt Abrev.
			WHEN 'FRI' THEN 6 WHEN 'FRIDAY'  THEN 6
			WHEN 'SAT' THEN 7 WHEN 'SATURDAY' THEN 7
			ELSE -1 -- we'll catch this & return null
			END
	IF @nDWofDAyName < 0 RETURN NULL -- Bad name	

	SET @nDays2Add2GetToFirstDay = 
         (7 - (@nFirstIsOnADW - @nDWOfDayName)) % 7

	SET @dtFirstOccurs = dateadd (d
                                , @nDays2Add2GetToFirstDay
                                , @dtFirstOfMonth)
		
	Set @onDate = dateadd(d
                        , 7 * (@nthOccurance - 1)
                        , @dtFirstOccurs )
	
	-- but check, is the day after the end of the month?
	IF MONTH(@OnDate) <> @nMonth SET @ONDATE = NULL
 
    RETURN @OnDate
END 
GO

GRANT EXEC on dbo.udf_DT_NthDayInMon TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

\-------Stop copying above this line ----/

Here are a few examples of how to use udf_DT_NthDayInMOn:

/------- Start copying below this line -\

select dbo.udf_DT_NthDayInMon (2004, 3, 4, 'Tue') 'Today'
     , dbo.udf_DT_NthDayInMon (2004, 3, 5, 'Tue') 'Next Tue'
     , dbo.udf_DT_NthDayInMon (2004, 4, 1, 'Thu') '1st Thurs'
GO

\-------Stop copying above this line ----/

Today       Next Tue    1st Thurs     
----------- ----------- -----------   
2004-03-23  2004-03-30  2004-04-01

The next set of tests shows some of the 5th days-of-the week in this month:

/------- Start copying below this line -\

select dbo.udf_DT_NthDayInMon (2004, 3, 5, 'Sun') '5th Sun'
     , dbo.udf_DT_NthDayInMon (2004, 3, 5, 'Mon') '5th Mon'
     , dbo.udf_DT_NthDayInMon (2004, 3, 5, 'Wed') '5th Wed'
     , dbo.udf_DT_NthDayInMon (2004, 3, 5, 'Thu') '5th Thu'
GO

\-------Stop copying above this line ----/

5th Sun    5th Mon     5th Wed     5th Thu
---------- ----------- ----------- --------
NULL       2004-03-29  2004-03-31  NULL

udf_DT_NthDayInMon is very useful when doing calendar calculations.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick