Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Adding weekdays to a date

    Hi,

    How can I add weekdays (a number) to a date and return the new date? For example if I add 10 weekdays to February 1st 2008, the result would be Feb 15th 2008.

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL>
    SQL> var thedate varchar2(20)
    SQL> var weekdays number
    SQL>
    SQL> exec :thedate := '01-02-2008'; :weekdays := 10;
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> with x
      2    as (
      3  select to_date( :thedate, 'dd-mm-yyyy' ) + rownum - 1 dt
      4    from dual
      5  connect by level <= ( ceil( :weekdays / 5 ) + 1 ) * 7
      6       )
      7  select dt
      8    from (
      9  select x.*,
     10         row_number( ) over( order by x.dt ) rn
     11    from x
     12   where to_char( dt, 'd' ) not in ( 7, 1 )
     13         )
     14   where rn <= :weekdays + 1
     15  /
    
    DT
    ---------
    01-FEB-08
    04-FEB-08
    05-FEB-08
    06-FEB-08
    07-FEB-08
    08-FEB-08
    11-FEB-08
    12-FEB-08
    13-FEB-08
    14-FEB-08
    15-FEB-08
    
    11 rows selected.
    
    SQL>

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    JMartinez, beware of the 'd' format that depends on the NLS parameter NLS_TERRITORY :
    Code:
    rbaraer@Ora10g> alter session set NLS_TERRITORY='AMERICA';
    
    Session altered.
    
    rbaraer@Ora10g> select to_char(sysdate, 'd') from dual;
    
    T
    -
    5
    
    rbaraer@Ora10g> alter session set NLS_TERRITORY='FRANCE';
    
    Session altered.
    
    rbaraer@Ora10g> select to_char(sysdate, 'd') from dual;
    
    T
    -
    4
    
    rbaraer@Ora10g>
    I personally use the 'DAY' format with the optional NLS parameter in TO_CHAR, but you could also get NLS_TERRITORY at the beginning of your procedure, change it so that your test on the 'd' format is correct and then set it back to its old value.

    Here is what I've used so far (sorry I use the french words ) :
    Code:
    TO_CHAR(dDate, 'DAY', 'NLS_DATE_LANGUAGE = FRENCH') NOT IN ('SAMEDI  ', 'DIMANCHE')
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    pretty good catch, RBARAER!

    I hope Spence23 make the corrections accordingly.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by JMartinez
    pretty good catch, RBARAER!

    I hope Spence23 make the corrections accordingly.
    Thanks

    I hope too.

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •