Home » Other » Training & Certification » Return all Saturday and Sundays between two specified date range
icon5.gif  Return all Saturday and Sundays between two specified date range [message #261272] Wed, 22 August 2007 04:29 Go to next message
Zafarul Islam
Messages: 3
Registered: August 2007
Junior Member
Return all Saturday and Sundays between two specified date range
Re: Return all Saturday and Sundays between two specified date range [message #261276 is a reply to message #261272] Wed, 22 August 2007 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try for the moment?
We don't do you homework but we can help you.

Don't forget to read and follow How to format your posts.
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Regards
Michel
Re: Return all Saturday and Sundays between two specified date range [message #261300 is a reply to message #261276] Wed, 22 August 2007 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There are two different approached I can think of off the top of my head.

1) Use a row generator to create a list of all the dates between the two days specifed, and then go through this list counting the Saturdays and Sundays

2) Work out the number of whole weeks between the two dates, double this number, and add on a correction for either of the start or end dates being in a weekend.

If this is an SQL homework, then the first answer is probably the one they want, but the second is a more efficient solution. (IMHO)
Re: Return all Saturday and Sundays between two specified date range [message #261305 is a reply to message #261272] Wed, 22 August 2007 05:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Zafarul Islam wrote on Wed, 22 August 2007 11:29
Return all Saturday and Sundays between two specified date range

Say "please". Wink

MHE
Re: Return all Saturday and Sundays between two specified date range [message #261328 is a reply to message #261305] Wed, 22 August 2007 05:58 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Here are some line of codes to generate

CREATE OR REPLACE FUNCTION DAY_CALCULATOR
  ( latest_date IN DATE DEFAULT trunc(sysdate), 
    earliest_date IN DATE)
  RETURN  number IS
   nCounter                 Number := 0;
   tempDate                 Date;
BEGIN
    tempDate := trunc(earliest_date);
    loop
    nCounter := nCounter + 1;
    tempDate := tempDate + 1;
    exit when  tempDate > trunc(latest_date);
    end loop;
    RETURN nCounter;
EXCEPTION
   WHEN others THEN
       return 0;
END;


after that run the query
select dates,to_char(dates,'day') from (
select to_date('01-Aug-2007')+level-1 as dates 
from dual connect by level <= DAY_CALCULATOR(sysdate,'01-Aug-2007'))
where trim(to_char(dates,'day')) in('saturday','sunday');


This is a example code for you question.


Cheers
Sanka
Re: Return all Saturday and Sundays between two specified date range [message #261346 is a reply to message #261328] Wed, 22 August 2007 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What exactly does DAY_CACULATOR do?

Is it actually the most processing intensive way yet of calculating the number of days between two given dates?
Why not do
floor(date1-date2)

(Not to mention the fact that you're passing a string into it instead of a date).

Re: Return all Saturday and Sundays between two specified date range [message #261370 is a reply to message #261328] Wed, 22 August 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Reported By: Zafarul Islam On: Wed, 22 August 2007 14:15

Reason My query is solved by this solution

Use "Reply" button and not "report message to a moderator" link to reply.

Regards
Michel

[Updated on: Wed, 22 August 2007 07:18]

Report message to a moderator

Re: Return all Saturday and Sundays between two specified date range [message #261388 is a reply to message #261346] Wed, 22 August 2007 08:11 Go to previous message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

JRowbottom wrote on Wed, 22 August 2007 16:57
What exactly does DAY_CACULATOR do?

Why not do
floor(date1-date2)

(Not to mention the fact that you're passing a string into it instead of a date).




Actually I was thinking that two dates can't be substract (as on the basis of - addition of two dates are not allowed). But I was wrong thanks for correcting me!

Cheers
Sanka
Previous Topic: Oracle Apps DBA Course
Next Topic: Query for joining columns of single table
Goto Forum:
  


Current Time: Tue Dec 06 14:27:37 CST 2016

Total time taken to generate the page: 0.08186 seconds