how to [message #38311] |
Tue, 09 April 2002 13:36 |
Sam
Messages: 255 Registered: April 2000
|
Senior Member |
|
|
How to get weekend dates for a given date range.
iam trying to do something like this.
input is startdate = '01/01/2002'
enddate = '04/01/2002'
i have to get return weekend dates for the above range,
Appreciate any help
|
|
|
Re: how to list weekend days [message #38312 is a reply to message #38311] |
Tue, 09 April 2002 15:10 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Just make sure that your all_objects view has at least as many rows as your date range (most installations will have at least several thousand rows in this view):
select :start_date + r weekend_date
from (select rownum - 1 r
from all_objects
where rownum <= (:end_date - :start_date + 1))
where to_char(:start_date + r, 'Dy') in ('Sat', 'Sun');
:start_date := to_date('01/01/2002', 'mm/dd/yyyy');
:end_date := to_date('04/01/2002', 'mm/dd/yyyy');
01/05/2002
01/06/2002
01/12/2002
01/13/2002
01/19/2002
01/20/2002
01/26/2002
01/27/2002
02/02/2002
02/03/2002
02/09/2002
...
03/24/2002
03/30/2002
03/31/2002
|
|
|
Re: how to list weekend days [message #38315 is a reply to message #38312] |
Tue, 09 April 2002 22:14 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
The 'r' is an alias for (rownum - 1) in the inline view. The inline view is going to return a set of numbers from 0 - (number of days in range + 1). You can run this part of the query by itself to see what it is returning.
Each of these numbers is then added to your start date and then checked to see if it is a weekend date or not.
The end result is that only weekend days in the specified date range are included.
|
|
|
|
Re: how to list weekend days [message #38324 is a reply to message #38312] |
Wed, 10 April 2002 13:10 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You can run it straight from a SQL*Plus prompt, but you'll have to supply values for the start_date and end_date variables. You can just hardcode them using to_date('01/01/2002', 'mm/dd/yyyy') and to_date('04/01/2002', 'mm/dd/yyyy') for example to try it out.
|
|
|