Date of the n-th Day in a Month

AID: 1639
  • Status: Published

3510 points

  • By
  • TypeTips/Tricks
  • Posted on2009-09-24 at 22:28:23
Ever wondered the date a "day" first occurred?

Realize I am not talking about the beginning of time?

Use SQL Server 2005?

Then this article is for you!

In this SQL Server 2005 tip you will see how to determine the date on which a requested day of the week occurred in any month of any year.  A typical scenario would be in a calendaring system that allows you to establish meetings on the first Monday of every month.  Consequently, as this function has evolved over time, its ability now goes beyond just the first occurrence and can return the second, third, ..., or even last occurrence of a given weekday.

Since a month can start on any day of the week, having an ordinal weekday calculation is not a trivial task.  For example: the first Monday in August 2009 fell on the 3rd of August; the first Tuesday for September 2009, September 1st; the last Tuesday of September 2008, September 30th.

With that in mind, let's begin going through the sections...

  • What you need; what you get.
  • Validate input and default values just in case.
  • It's all in the date.
  • The User Defined Function Code.


1

What you need; what you get.


Given a year and month, our function will get a specific weekday occurrence using its integer day of week value (each locale may use a different @@datefirst value, but, typically for the US, Sunday is 1; Monday, 2; etc.) and numbered instance representing 1st, 2nd, 3rd, and so on.  

To make it easy to find the last occurrence, which is difficult when number of times a day appears in a month is unknown, we have a secondary ranking that orders in reverse; therefore, optionally, we can set a flag (e.g., @lastFirst) that indicates the last occurrence should be treated as row #1.  With this capability, we can simply search for @occurrence = 1 with @lastFirst = 1.  The 2nd to last day would then be @occurrence = 2 and so on...

After processing these inputs, a datetime value will be returned that represent the date on which this n-th day in the month occurs by matching the criteria above.

Here is a look at how that translates to the function's signature and skeleton structure:

create function dbo.fn_OccurrenceOfWeekdayInMonth(
   -- input
   @year int
 , @month tinyint
 , @weekday tinyint
 , @occurrence tinyint
 , @lastFirst bit
)
returns datetime
as 
begin
   -- local variables / validated input
   declare @result datetime
         , @yr int
         , @mo tinyint
         , @dw tinyint
   ;

   -- processing
   ; 
  
   -- output
   return @result ;
end
                                    
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select allOpen in new window




Are you sure you know what you need?

2

Validate input and default values just in case.


In SQL Server, including version 2005, the datetime data type is limited to years 1753 to 9999.
(DATE and TIME ... don't be scared, and do it right (the first time))

if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
else set @yr = @year
                                    
1:
2:

Select allOpen in new window



Only 12 months in a year:

if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
else set @mo =  @month
                                    
1:
2:

Select allOpen in new window



Only 7 days in a week:

if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
else set @dw = @weekday
                                    
1:
2:

Select allOpen in new window



For this implementation, I decided to return null for invalid occurrence values.  Rationale is that the above verification is all that is required to establish valid initialization.  If I ask for the 10th Monday in August, then null is pretty reasonable response.  Disagree?  No worries, modify this once you understand the base functionality.



Moving on...

3

It's all in the date.


Why re-invent the wheel?  The datetime object keeps track of it all for us: the day of the week, the week, the month, the year; therefore, the trick is to construct a virtual table of all the dates in the month (and, if your adventurous, any given duration of time).  Once we have our table of dates, we can take advantage of the natural ordering and date functions of the datetime type.

To construct our virtual table, we take advantage of common table expressions and their recursive capabilities:
(Not going to go into too much detail here on how recursion works in CTE; therefore, I would recommend you read this article by EE's own Aaron Akin)

with dates( dt )
as
(
   -- select 1st day of month constructed from year and month inputs
   select convert( datetime, 
            convert( char(8), 
               @yr * 10000 + @mo * 100 + 1 
            ) 
         ) 

   union all -- facilitates recursion

   -- add in remainder of days in month
   select dt + 1
   from dates
   -- keeps adding until the next day would be 1st of next month
   where day( dt + 1 ) <> 1 
)
                                    
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Select allOpen in new window



So, we just create a base record with the first day of the month and then used union to join that to a selection of this first record +1.  Moreover, CTEs are virtual tables so our new second row is subject to same select  ... from dates statement and so as long as adding a day via +1 doesn't violate the where clause filtering, which checks that the day of the month of the new date is not 1 (meaning 1st of next month), we will continue to get one new one recursively until our table is filled with all the dates in the month.  

Try it, by simply adding this select immediately after the CTE definition above:
(note you will have to have @yr and @mo variables from above declared and set appropriately)

select dt from dates ;
                                    
1:

Select allOpen in new window


For September 2009, you should see these results :

datesCTE.JPG
  • 56 KB
  • Dates CTE Results
Dates CTE Results



However, for our usage, we need more substance than this, but instead of trying to do the date plus calculation at the same time we do other calculations, I opted to keep the recursion separate and utilize the "nesting" capabilities of CTE which I again feel Aaron has done a great job explaining.

So adding our second CTE definition would look like this:

, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
   select dt, datepart( weekday, dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt desc )
   from dates
)
                                    
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select allOpen in new window



Note the comma (,) replaced the WITH keyword and joins to the two CTE statements together (so if you were wondering, yes, the "with dates ..." is still in the code).  Additionally, we are also adding our value-add information like the day of the week for each date along with its ordinal ranking with respect to other dates for same day of the week.  Using the row_number() function with both ascending and descending order by dt (date) is the key to our aforementioned ability to search the last occurrence as easily as the first.  This way @occurrence = 1 coupled with value of @lastFirst gives us the correct of the two occurrences.

For more detailed information on row_number() and other analytical ranking functions within SQL Server 2005, please refer to my EE article in reference.

Think that about does it, but if you are curious then try it similar to what we did above:
(as mentioned, remember to declare and set variables as well as dates CTE)

select dt, dw, occurrence, occurrence_reverse
from dates_tagged
; 
                                    
1:
2:
3:

Select allOpen in new window


Or, for your convenience, you can download and execute this T-SQL script :

datesTaggedCTE.sql.txt
  • 939 bytes
  • Dates Tagged CTE Script
datesTaggedCTE.sql.txt


And the new results for September 2009 :

datesTaggedCTE.JPG
  • 75 KB
  • Dates Tagged CTE Results
Dates Tagged CTE Results




Now, putting it all together...

4

The User Defined Function Code.


We add processing to the function by first creating a virtual table of all the dates in the requested month and then utilizing analytical functions determine the occurrence number for each weekday. 

alter function dbo.fn_OccurrenceOfWeekdayInMonth(
   @year int
 , @month tinyint
 , @weekday tinyint
 , @occurrence tinyint
 , @lastFirst bit
)
returns datetime
as 
begin
   -- local variables
   declare @result datetime
         , @yr int
         , @mo tinyint
         , @dw tinyint
   ; -- handle invalid year value
   if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
   else set @yr = @year
   ; -- handle invalid month value
   if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
   else set @mo =  @month
   ; -- handle invalid day of week value
   if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
   else set @dw = @weekday
   ; -- construct date time common table expression to make things easier
   with dates( dt )
   as
   (
      -- select 1st day of month constructed from year and month inputs
      select convert( datetime, 
               convert( char(8), 
                  @yr * 10000 + @mo * 100 + 1 
               ) 
            ) 

      union all -- facilitates recursion

      -- add in remainder of days in month
      select dt + 1
      from dates
      -- keeps adding until the next day would be 1st of next month
      where day( dt + 1 ) <> 1 
   )
   -- utilize the cte of dates and tag each with occurrence value,
   -- using ranking function(s)
   , dates_tagged( dt, dw, occurrence, occurrence_reverse )
   as
   (
      select dt, datepart( weekday, dt )
           , row_number() 
                over( partition by datepart( weekday, dt ) 
                      order by dt )
           , row_number() 
                over( partition by datepart( weekday, dt ) 
                      order by dt desc )
      from dates
   )
   select @result = dt
   from dates_tagged
   where dw = @dw
     and ((@lastFirst = 1 and occurrence_reverse = @occurrence)
      or (coalesce(@lastFirst, 0) = 0 and occurrence = @occurrence))
   ; -- return result to caller
   return @result ;
end
                                    
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:

Select allOpen in new window



The code should speak for itself and be relatively straight forward to follow; however, please leave comments below if you want further clarification or explanations.

Example usage:

select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 9, 1, 1, 0 ) ;
                                    
1:

Select allOpen in new window


So, the first time the first day of the week (e.g., Sunday for me) occurs in September 2009 is:

2009-09-06 00:00:00.000
                                    
1:

Select allOpen in new window




...And that's it!

We now have another function to add to our utilities database / tool-belt that allows us to find a specific occurrence of a certain day of the week in a given month.  With this being simple, the value here is its flexibility to be used regardless of your local settings for which day of the week is which 1 - 7 value.  Consequently, as a bonus for reading, you will find in the appendix a make-shift function overload allowing you to call this utility with a day name in your given locale.

Thanks for reading!

For some more fun and a real-world application of this article's function, please read this article:

   DST Utilities Table Creation : Using occurrence of a weekday practically!

Happy coding!

Best regards,

Kevin (aka MWVisa1)


Appendix A: A more human-friendly invocation.

Here is a wrapper function to allow us to call our weekday code by day name (e.g., Tuesday (English) or Martes (Spanish) or Dienstag (German) -- language doesn't matter) instead of the day number.
create function dbo.fn_OccurrenceOfDaynameInMonth(
   @year int
 , @month tinyint
 , @dayname varchar(15)
 , @occurrence tinyint
 , @lastFirst bit
)
returns datetime
as 
begin
   -- local variables
   declare @result datetime
         , @weekday tinyint
   declare @daynames table( 
              dayname varchar(15) primary key
            , weekday tinyint 
           )
   ; -- translate day name to day of week number
   set @weekday = 1 -- use week day variable temporarily as counter
   while (@weekday <= 7)
   begin
      -- use result variable temporarily to store dates
      set @result = getdate() - @weekday
      insert into @daynames 
      values( datename( weekday, @result )
            , datepart( weekday, @result ) 
      )
      set @weekday = @weekday + 1 -- increment counter
   end
   select @weekday = weekday 
   from @daynames 
   where dayname = @dayname
   ; -- utilize dbo.fn_OccurrenceOfWeekdayInMonth to get result
   set @result = 
      dbo.fn_OccurrenceOfWeekdayInMonth(
         @year, @month, @weekday, @occurrence, @lastFirst
      )
   ; 
   return @result ;
end
                                    
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

Select allOpen in new window



Example usage:
select dbo.fn_OccurrenceOfDaynameInMonth( 2008, 2, 'Thursday', 1, 1 ) ;
                                    
1:

Select allOpen in new window


So, the last (notice 1 for the @lastFirst parameter) Thursday in February 2008 is:
2008-02-28 00:00:00.000
                                    
1:

Select allOpen in new window



(alternate language example)
-- Change language to French if in English system as I am;
-- users already in French system, remove both set lines.
set language French ;
-- Samedi == Saturday
select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 2, 'Samedi', 1, 1 ) ;
set language English ;
                                    
1:
2:
3:
4:
5:
6:

Select allOpen in new window


Le dernier samedi de Février 2009 est:
(last Saturday in February 2009)
2009-02-28 00:00:00.000
                                    
1:

Select allOpen in new window



Challenge:
Can you guess the dates for the following?
  • the last Friday in February 2008
  • the last Wednesday in February 1900
  • the last Thursday in February 1900


Utilize our new code to check your answers to the above.  

Have fun!


Related Resources / References:

Analytical SQL : Where do you rank?
http://www.experts-exchange.com/A_1555.html

Common Table Expressions (WITH() clause)
http://msdn.microsoft.com/en-us/library/ms190766.aspx
(nice reference on nested and recursive CTEs by Aaron Akin)
http://sqlservernation.com/blogs/development/archive/2009/03/18/nested-common-table-expressions.aspx
http://aaronakinsql.wordpress.com/2009/01/20/recursive-common-table-expressions-part-1/

DATE and TIME ... don't be scared, and do it right (the first time)
http://www.experts-exchange.com/A_1499.html

DATEFIRST
http://msdn.microsoft.com/en-us/library/ms187766(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

DST Utilities Table Creation : Using occurrence of a weekday practically!
http://www.experts-exchange.com/A_1640.html

 
Asked On
2009-09-24 at 22:28:23ID1639
Tags

SQL Server 2005

,

User Defined Function

,

Occurrence of specific day of week

,

T-SQL Utilities

Topic

SQL Server 2005

Views
2504

About The Author

mwvisa1

Kevin has been in IT since 1999 as an administrator, an analyst, an architect, a developer and, most recently, a manager. A passionate SQL and Java developer, his motto is "keep learning and/or keep smiling; there's nothing worse than a sad fool..."

Comments

Expert Comment

by: aikimark on 2009-10-14 at 19:17:23ID: 4235

I just posted a link to this article for someone needing a date range in the SQL Server TA.   A first for me.

Author Comment

by: mwvisa1 on 2009-10-14 at 19:39:46ID: 4237

Thanks, aikimark.
Comes in handy every once in a while.  

Author Comment

by: mwvisa1 on 2009-10-14 at 19:46:53ID: 4238

By the way, this is a good tool when particular date (occurrence) is needed, but what also comes up is needing to plug gaps so basically get all the dates in a specific range.  

For that you need a table of dates or numbers that you can associate to dates :

I showed one technique using recursive CTE above as part of the date occurrence solution, but here are some other articles that are good reads on this topic :

Delimited String Parsing in SQL Server 2005 and later
http://www.experts-exchange.com/A_192.html (see vw_nums)

Fun with MS SQL spt_values for delimited strings and virtual calendars
http://www.experts-exchange.com/A_1221.html

Expert Comment

by: meeran03 on 2010-08-22 at 09:57:51ID: 18586
Thanks mwvisa1 . great article.
soon it may come in handy for me..!

Author Comment

by: mwvisa1 on 2010-08-22 at 10:03:23ID: 18587
Glad to hear it, meeran03!
Thank you for the vote also as well as others I had not yet acknowledged. I am just happy to have helped in some way.

Best regards,
Kevin

Add your Comment

Please Sign up or Log in to comment on this article.

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

See Plans and Pricing

30-day free trial. Register in 60 seconds.

Loading Advertisement...

Top MS SQL Server 2005 Experts

  1. ScottPletcher

    195,617

    Guru

    8,500 points yesterday

    Profile
    Rank: Genius

  2. jogos

    176,191

    Guru

    668 points yesterday

    Profile
    Rank: Sage

  3. acperkins

    140,953

    Master

    1,000 points yesterday

    Profile
    Rank: Genius

  4. TempDBA

    113,707

    Master

    1,168 points yesterday

    Profile
    Rank: Sage

  5. matthewspatrick

    93,824

    Master

    1,600 points yesterday

    Profile
    Rank: Savant

  6. lcohan

    93,302

    Master

    2,000 points yesterday

    Profile
    Rank: Genius

  7. dtodd

    84,612

    Master

    0 points yesterday

    Profile
    Rank: Genius

  8. mwvisa1

    76,166

    Master

    0 points yesterday

    Profile
    Rank: Genius

  9. ValentinoV

    76,011

    Master

    1,800 points yesterday

    Profile
    Rank: Genius

  10. ralmada

    55,844

    Master

    400 points yesterday

    Profile
    Rank: Genius

  11. anujnb

    54,164

    Master

    0 points yesterday

    Profile
    Rank: Wizard

  12. angelIII

    53,846

    Master

    10 points yesterday

    Profile
    Rank: Elite

  13. EugeneZ

    53,602

    Master

    0 points yesterday

    Profile
    Rank: Genius

  14. HainKurt

    49,150

    0 points yesterday

    Profile
    Rank: Genius

  15. Buttercup1

    48,568

    0 points yesterday

    Profile
    Rank: Master

  16. huslayer

    40,600

    0 points yesterday

    Profile
    Rank: Sage

  17. appari

    39,400

    0 points yesterday

    Profile
    Rank: Genius

  18. tim_cs

    34,200

    0 points yesterday

    Profile
    Rank: Wizard

  19. wdosanjos

    33,836

    0 points yesterday

    Profile
    Rank: Genius

  20. dqmq

    31,136

    0 points yesterday

    Profile
    Rank: Genius

  21. Cluskitt

    30,940

    0 points yesterday

    Profile
    Rank: Wizard

  22. SJCFL-Admin

    30,877

    0 points yesterday

    Profile
    Rank: Master

  23. jimhorn

    29,975

    0 points yesterday

    Profile
    Rank: Genius

  24. Brichsoft

    28,107

    0 points yesterday

    Profile
    Rank: Sage

  25. momi_sabag

    27,903

    0 points yesterday

    Profile
    Rank: Genius

Hall Of Fame