Solved

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

Posted on 2001-07-19
5
1,478 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

679 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