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

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

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
Onkar
Asked:
Onkar
1 Solution
 
Dave_GreeneCommented:
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
 
KDivadCommented:
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
 
OnkarAuthor Commented:
Thanks for the Answer.
But actually I wanted a SQL Query for this

Thanks
Onkar
0
 
inthedarkCommented:
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
 
inthedarkCommented:
Thankfully I see you have an answer from Dave_Greene.
0

Featured Post

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.

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