# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « SQL Saturday 33 - Charlotte, NC Do You Get A Benefit From Compressing Backups If You Already Have Compressed Data? »

# Calculating Nth weekday in a month

by Ramireddy on Mar 02, 2010 in categories Database Programming, Microsoft SQL Server

How to calculate the Nth week day of a month?

This will be useful in most of the Scheduling problems like Scheduling Jobs, Appointments etc.

Let me demonstrate how we can achieve this:

Let us take an example of say, 3rd Sunday in February, 2010 will come on 21st February, 2010.
The solution to the above problem needs to satisfy 3 conditions.
1. The date should be in the specified month and year.
2. The date should be in the specified week number.
3. The date should be of the specified week day.
Solving this problem comprises the following 3 steps, which logically satisfy each of the above conditions:
1. Get the first date for the provided month and year. E.g., the first date for the specified month (February) and Year (2010) is February 1st 2010.The following query will give you the first date for provided month and year.

tsql

2. Get the first date on which any weekday will occur nth time in the month. i.e. ((N-1)*7+1)th day of the month will be the first date on which any weekday will occur nth time. In this case, the first date for the specified week (3) is, February 1st 2010 + (3 - 1)*7 days = February 15th 2010.

tsql

3. Now add the number of days required to calculate the specified weekday. This will depend on the weekday specified and the weekday of the first date that came in the above step. These will be based on the weekday specified and the weekday of the first date that came in above step.
Week day Requested is 6 (Sunday) and Weekday of first date that came in above step is 0(Monday).
So, No of days required to add are (Weekday Requested - weekday of first date ) = 6 – 0 = 6 days.
Note: If the above formula results in negative value, add 7 to the result.
You can write the above expression as
No of days to add = (7 + Weekday Requested - Weekday Of first date) % 7
In our case, the number of days required to add are 6 - 0 = 6 days. So, by adding 6 days to 15th February 2010,you will get 21st February 2010, which is 3rd Sunday of February 2010.
So, the final query will become,

tsql

Finally, the function written below is the complete solution that will be useful in calculating Nth weekday of any given month and year.

tsql
1. CREATE FUNCTION fn_getWeekDay (@YEAR INT,@MONTH INT,@WeekNo INT,@WeekDay INT)
2.     RETURNS DATETIME
3.     AS
4.     BEGIN
5.         RETURN (
8.     END

The above function takes 4 parameters: year, month, week number and weekday (Monday-0, Tuesday-1, Wednesday-2, Thursday-3, Friday-4, Saturday-5, Sunday-6)
So, in order to calculate 3rd Sunday in February 20010, you need to call the function like below.

tsql
1. SELECT dbo.fn_getWeekDay(2010,2,2,3)

I hope my function can be useful for you and see you in my next blog.

3854 views

Comment from: Picatrix [Member]
This is pretty sweet thanks for that, I needed something like that in my database
03/03/10 @ 11:11
Comment from: NZ [Visitor]
It's simply gr8.Thank you so much for this.
12/30/11 @ 10:30
Comment from: Kiran Diwan [Visitor]
It is good...
Thanks,
02/08/12 @ 08:36