[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Date Selection

I need an SQL query that I can pass a month to, it will return 3 values.

1. The week numbers of the weeks contained in that month
2. The date of Monday in each of those weeks (or the first day of that week if the month starts midweek.
3. The date of Friday in each of those weeks (or the last day of that week if the month ends midweek.

Thanks in advance
4 Solutions
Jesus RodriguezIT ManagerCommented:
You will need a store procedure for that and work with the date functions of sql
mgordon-spiAuthor Commented:
Thanks for those links. I am struggling with getting this output, I am hoping to get some help with a Query that will return those values when passing a month value.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Scott PletcherSenior DBACommented:
Need some clarification.

What is your definition of "week" and "week number"?

On what day does a "week" start?

How do you count "weeks" in a year, i.e., if the first day of the year is a Thurs (Jan 1 = Thu), what specific dates are the first "week" of the year, and the second?

Similarly, if Jan 1 happened to be Sat, in that case, what would the specific dates of the two "weeks" of the year be?
mgordon-spiAuthor Commented:
On the Calendar every week has a number. I need to be able to calculate the dates for each week in a month from Monday to Friday.


For the month of July 2011 I need it to return the following

Week 26 Fri 1st
Week 27 Mon 4th to Fri 8th
Week 28 Mon 11th to Fri 15th
Week 29 Mon 18th to Fri 22nd
Week 30 Mon 25th to Fri 29th

So I need it to tell me the month spans 5 weeks, what those week numbers are. The dates of the weeks from Monday to Friday. The part weeks at the beginning and end of the month, I need the dates.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<since this question is still open>

Here's an article on how to build your own calendar table in SQL Server.  If you execute the code up top to build the table, then scroll down to see how you can query the table, you should be able to piece together a simple query to meet your needs.
mgordon-spiAuthor Commented:

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now