|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
SQL Server T-SQL User-Defined Function of the Week
Find the Nth Day-of-the-week in a MonthVolume 2 Number 14 March 23, 2004
Check out the UDF Frequently Asked Questions at:
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 'TUESDAY' 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 ----/(Results - with time removed.)
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 ----/(Results - with time removed.)
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: