?
Solved

Finding Number of Saturdays and Sundays between range of a date.

Posted on 2001-07-19
5
Medium Priority
?
1,481 Views
Last Modified: 2008-03-10
Hi

Wanted a number Saturdays and Sundays between a given date range.

Suppose the date is From 01/Jan/2001 to 15/Mar/2001

I want a SQL query to find Number of Saturdays and Sundays between these two dates.

Please any one will help me out, which will be greate thing for me.

Thanks in Advance
Onkar
0
Comment
Question by:Onkar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 600 total points
ID: 6299304
I don't know about a Query being able to do this... but here is some code to...

Option Explicit

Private Sub Command1_Click()
  Dim i As Long
  Dim iDiff As Long
  Dim strDay As String
  Dim newDate As Date
  Dim Weekend As Long
 
  iDiff = DateDiff("d", txtDate1, txtDate2)
 
  newDate = txtDate1.Text
 
  For i = 1 To iDiff
    strDay = Format(newDate, "DDD")
    If strDay = "Sat" Or strDay = "Sun" Then
      Weekend = Weekend + 1
    End If
    newDate = DateAdd("d", 1, newDate)
  Next i
 
  MsgBox "Total number of weekend days = " & Weekend
 
End Sub

Private Sub Form_Load()
  txtDate1.Text = Date
  txtDate2.Text = DateAdd("m", 2, Date)
End Sub
0
 
LVL 5

Expert Comment

by:KDivad
ID: 6299664
What about something using this:

=====help file excerpt=====
If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.
===========================

Should be simple enough to check for Saturdays as well...
0
 

Author Comment

by:Onkar
ID: 6300875
Thanks for the Answer.
But actually I wanted a SQL Query for this

Thanks
Onkar
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6300876
Do you have a file with lots of records in.  The problem with this is that you may not have a record in the file for each of the days.  I wrote a job schedling system for a factory and needed to do this type of query.

First I created a table with all of the days of the year.  The file is created automatically a sub detects at startup is the table needs the records for the next year.

You can then so a simple query like:

SELECT Format([masterdates],"ddd") AS [Day], ordline.*
FROM MasterDatesTable INNER JOIN ordline ON ordhdr.date_on_file = ordline.webconn_number
WHERE (((Format([masterdates],"ddd"))="Sat" Or (Format([masterdates],"ddd"))="Sun"));

The list of days in the years are stored in the table called MasterDatesTable.  The above query will return all records in the ordline table on a saturday or sunday.

So when reporting you can see ALL Saturdays and Sundays and not hust the days where an order was made.

The example provided was produced from Access I will convert it to SQL. Back in a minute.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6300881
Thankfully I see you have an answer from Dave_Greene.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question