Solved

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

Posted on 2001-07-19
5
1,476 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
5 Comments
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 200 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now