SQL: Find the Week Number in a Month
Categories: Database Administration, Information Technology, OracleAjama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month.
Basically if your week starts on a Sunday, anything leading up to the first Sunday would be week 1, then the first complete week, Sunday through Saturday would be week 2, then week 3, etc. If the month happens to start on a Sunday week 1 would be a complete week.
If you are unfamiliar with manipulating dates in Oracle, you may want to check out my other articles on Oracle, SQL, Dates and Timestamps and Performing Math on Oracle Dates.
Starting simple I worked out how to find the first day of the month. The TRUNC
function will allow you to truncate a date to a certain precision, so I started by truncating the date to the month. Today is February 1, so since we’re using sysdate
for some testing we coincidentally get back February 1.
SQL> select trunc(sysdate, 'MM') from dual;
TRUNC(SYS

01FEB06
Now we have a starting point. From here I used the NEXT_DAY
function to find the beginning of the first week of the month. This is found by looking at the first of the month and the 6 days leading up to it. Since next_day
excludes the day it is calculating from we subtract 7 for the start date.
SQL > select next_day(trunc(sysdate, 'MM')  7, 'Sunday') from dual;
NEXT_DAY(

29JAN06
Now we have established the beginning of the first week in the month. The first week starts at the end of the previous month as it is only partially in February. With this date in mind we can now calculate how many days we are from then:
SQL> select sysdate  next_day(trunc(sysdate, 'MM')  7, 'Sunday') from dual;
SYSDATENEXT_DAY(TRUNC(SYSDATE,'MM')7,'SUNDAY')

3.55945602
This tells us we are 3.559 days from the beginning of the first week in the month. To express that in weeks we simply divide by 7.
SQL> select (sysdate  next_day(trunc(sysdate, 'MM')  7, 'Sunday'))/7 from dual;
(SYSDATENEXT_DAY(TRUNC(SYSDATE,'MM')7,'SUNDAY'))/7

.508627646
This tells us that we are about .5 weeks from that first Sunday. We don’t care about the fractional part so we can use the other form of the TRUNC
function to to truncate this to a whole number. That would put us in the 0th week of the month, but since we want to start counting at 1 we add 1 to the number.
SQL> select trunc(((sysdate  next_day(trunc(sysdate, 'MM')  7, 'Sunday'))/7),0) + 1
from dual;
TRUNC(((SYSDATENEXT_DAY(TRUNC(SYSDATE,'MM')7,'SUNDAY'))/7),0)+1

1
This tells us accurately that right now we’re in the first week of the month. Now we want to try this logic out with some other dates. It’s easiest to do that if we just create a function for it.
CREATE OR REPLACE FUNCTION week_in_month
(check_date DATE DEFAULT sysdate, week_start CHAR DEFAULT 'Sunday')
RETURN number
IS
week_number NUMBER;
BEGIN
select trunc(((check_date  next_day(trunc(check_date, 'MM')  7, week_start))/7),0) + 1 into week_number from dual;
RETURN week_number;
END;
/
Here I’ve created a function week_in_month to execute this SQL with two parameters. The first, check_date
can be any Oracle date. The second parameter, week_start
is the day that your week starts. This must be spelled out, like ‘Sunday’, ‘Monday’, etc. The function will return the week number from start of month.
If the check_date is not specified, the current date and time will be used. If the week_start is not specified we’ll default to Sunday.
Now let’s try our function without parameters.
SQL> select week_in_month from dual;
WEEK_IN_MONTH

1
The function correctly identifies that we’re in week 1 of the month by our original definition. Now we’ll look at a few other examples. Remember, where we have not specified a starting day our function will assume Sunday.
SQL> select week_in_month(to_date('01/29/06','MM/DD/YY')) from dual;
WEEK_IN_MONTH(TO_DATE('01/29/06','MM/DD/YY'))

5
Here we see that 1/29/06 was in the 5th week of January.
SQL> select week_in_month(to_date('02/06/06','MM/DD/YY')) from dual;
WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'))

2
This shows that February 6 will be in the second week in February.
SQL> select week_in_month(to_date('02/06/06','MM/DD/YY'), 'Wednesday') from dual;
WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'),'WEDNESDAY')

1
Here we have specified that our week starts on Wednesday, and based on that February 6th will be in the first week in February.
SQL> select week_in_month(to_date('04/30/06','MM/DD/YY')) from dual;
WEEK_IN_MONTH(TO_DATE('04/30/06','MM/DD/YY'))

6
This is a somewhat rare instance, and it may look like there’s a problem with the logic, but because of how April of 2006 falls, the 30th would actually be in the 6th week of the month based on our criteria.
15 Responses to “SQL: Find the Week Number in a Month”

David Lefler Says:
March 11th, 2006 at 5:09 pmJon,
Just wanted to tell you that this code worked GREAT and it saved me a ton of time. This was exactly what my user wanted! I created an Oracle function and called it from Informatic.
Thanks again!
David 
Hubris Sonic Says:
July 7th, 2006 at 4:14 amnice work! you saved me a ton of time also, Oracle date math gives me a headache…

Sanjay Kumar Says:
November 24th, 2006 at 3:37 pmHi Jon,
Your query works fine for a single date.
If a range of dates say between 15Jan 2006 and 21May2006 I want to find out which are the days with Mondays.
how can i achieve this?Regards,

Jon Emmons Says:
November 27th, 2006 at 9:27 pmSanjay,
I don’t quite understand what you’re looking for. Are you trying to find every Monday between these dates? If so you will probably need to parse over the range with a PL/SQL loop.
Does that make sense?

Uma Says:
December 6th, 2006 at 12:00 pmI am trying to find 2nd sunday in March for DST change of 2007. For some reason for the year 2009 I did not get an accurate 2nd Sunday using next_day function. Help please!

lakshmi Says:
December 15th, 2006 at 5:17 ami am trying to find the number of weeks in a particular month. Help please !!

Jon Emmons Says:
December 16th, 2006 at 11:12 amSorry for my delayed response. I’m not sure if you’ve found out the issue with this, but since March 2009 starts on a Sunday the next_day function will move forward from that and skip the day provided.
Try subtracting one day from the date before applying the next_day function will assure you that you always include the date provided as one of the days indicated.
Hope this helps.

Shiva Says:
January 22nd, 2007 at 4:09 pmHi,
This is Shiva here. I have completed my B.E.(ECE) in 2004. For the past 2 years, I was working in a BPO. But now I have changed my mind set and tyring to do Oracle Applications.
So my question now is whether SAP is best or Oracle Applications is best. Please give me a solution please, as I don’t have time, because the course is going to start.
Thanks and Regards,
Shiva.S. 
Shiva Says:
January 22nd, 2007 at 4:14 pmHi All!
This is Shiva here. I don’t have knowledge on both Oracle D2K and PL/SQL. I am blank.
My Question is that is it possible to complete Oracle D2k and PL/SQL within a month and shall i able to complete the OCA exam successfully?
Because, I need to get the OCA certification with in a month.
Please solve my doubts and queries..
Thanks and Regards,
Shiva.S. 
Findy E. Says:
April 2nd, 2007 at 2:38 amHi Jon,
Thanks for your query. It’s work fine and very helpful.
Findy

Magesh Says:
April 13th, 2007 at 4:32 amHi i want to find number of sundays in a month…,

p.warawit Says:
March 26th, 2008 at 10:36 pmJon,
Thank you very much. Your work have saved me hours.
P.Warawit.

bzi Says:
March 26th, 2008 at 11:52 pmHi Jon,
Your function works great for me. I wrote up a quick blog entry that use your code but explain it in my Thai language so that people in my country can leverage your expertise. I gave you full credit of the code and linked back to this post.
http://www.bzinsight.net/plsqlfindtheweeknumberinamonth
Best Regards,
BzInsight.net 
Murali Says:
May 7th, 2010 at 1:20 amThis is great trick.
Thank You!
Murali 
NKG Says:
September 23rd, 2011 at 7:25 amHi,
pls do the help for me.. thanks in advance..assume that the input parameter date is “23sep2011”
i want to retrieve the one week data from the table
i.e the previous sunday date is 18sep2011. (sunday)
the next saturday date is 24sep2011.(saturday)ultimately i want 1 week data.. the week starts from sunday and ends at Saturday..