OraFAQ Forum: SQL & PL/SQL » Connect by level date range

Home » SQL & PL/SQL » SQL & PL/SQL » Connect by level date range - excluding weekends

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Connect by level date range - excluding weekends [message #165213]

Tue, 28 March 2006 23:29

danny_t
Messages: 52
Registered: March 2005

Member

I'm currently using:

SELECT <your_start_date>+rownum-1 thedate
  FROM ( SELECT 1
           FROM dual
         CONNECT BY level <= <your_end_date>-<your_start_date>+1
       )
/

to select a date range. Is it possible to use this but somehow exclude weekends?

Cheers,

Dan

Report message to a moderator

Re: Connect by level date range - excluding weekends [message #165223 is a reply to message #165213]

Wed, 29 March 2006 00:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

  1  select thedate
  2  ,      to_char(thedate, 'DY') dayname
  3  from   (SELECT to_date('01-04-2006', 'dd-mm-yyyy') + rownum - 1 thedate
  4          FROM   ( SELECT 1
  5                   FROM dual
  6                   CONNECT BY level <= to_date('15-04-2006', 'dd-mm-yyyy') - to_date('01-04-2006', 'dd-mm-yyyy') + 1
  7                 )
  8         )
  9* where  to_char(thedate, 'DY') not in ('SAT', 'SUN')
SQL> /

THEDATE    DAY
---------- ---
03-04-2006 MON
04-04-2006 TUE
05-04-2006 WED
06-04-2006 THU
07-04-2006 FRI
10-04-2006 MON
11-04-2006 TUE
12-04-2006 WED
13-04-2006 THU
14-04-2006 FRI

10 rows selected.

Instead of 'SAT' and 'SUN', you may need to use the abbreviations of your own language-settings.

hth

Report message to a moderator

Re: Connect by level date range - excluding weekends [message #165238 is a reply to message #165223]

Wed, 29 March 2006 00:47

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Frank wrote on Wed, 29 March 2006 08:00

Instead of 'SAT' and 'SUN', you may need to use the abbreviations of your own language-settings.

Or you could use the more general approach of the 'D' format mask: Sunday is day 1 and Saturday day 7. This works in any language:

SQL> select thedate
  2  ,      to_char(thedate, 'DY') dayname
  3  from   (SELECT to_date('01-04-2006', 'dd-mm-yyyy') + rownum - 1 thedate
  4          FROM   ( SELECT 1
  5                   FROM dual
  6                   CONNECT BY level <= to_date('15-04-2006', 'dd-mm-yyyy')
  7                                     - to_date('01-04-2006', 'dd-mm-yyyy') + 1
  8                 )
  9         )
 10  where  to_char(thedate, 'D') not in (7, 1)
 11  /

THEDATE   DAY
--------- ---
03-APR-06 MON
04-APR-06 TUE
05-APR-06 WED
06-APR-06 THU
07-APR-06 FRI
10-APR-06 MON
11-APR-06 TUE
12-APR-06 WED
13-APR-06 THU
14-APR-06 FRI

10 rows selected.

MHE

Report message to a moderator