Solved

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

20 Experts available now in Live!

Get 1:1 Help Now