Forum Stats

  • 3,874,068 Users
  • 2,266,672 Discussions
  • 7,911,721 Comments

Discussions

Calculate Last Working Day

sliderrules
sliderrules Member Posts: 951
edited May 15, 2013 8:47AM in SQL & PL/SQL
Hi,

I am using Oracle Database 11.2.1 and would like to return the last working day (DD/MM/YYYY).

The working days run from Mon-Fri

e.g. On Monday 18/06/2000 the value returned would be Fri 15/06/2000
Tagged:

Best Answer

  • jeneesh
    jeneesh Member Posts: 7,168
    edited May 15, 2013 8:46AM Answer ✓
    sliderrules wrote:
    I meant the working days of the week

    e.g. Mon 13/05/2013 would return 10/05/2013
    Tue 14/05/2013 would return 13/05/2013 etc
    What about the query already provided?
    SQL> with t as
      2      (
      3      select to_date('13/05/2013 ','dd/mm/yyyy') dt
      4      from dual
      5      union all
      6      select to_date('14/05/2013 ','dd/mm/yyyy') dt
      7      from dual
      8      )
      9      select dt,dt -
     10             case to_char(dt,'fmday','nls_language=english')
     11                         when 'sunday' then 2
     12                 when 'monday' then 3
     13                else 1
     14            end last_w_day
     15     from t;
    
    DT        LAST_W_DA
    --------- ---------
    13-MAY-13 10-MAY-13
    14-MAY-13 13-MAY-13

Answers

  • jeneesh
    jeneesh Member Posts: 7,168
    edited May 15, 2013 7:51AM
    18/06/2000 is not monday ;)
    18/06/2001 is..
    SQL> with t as
      2  (
      3  select to_date('18/06/2001','dd/mm/yyyy') dt
      4  from dual
      5  )
      6  select dt -
      7         case to_char(dt,'fmday')
      8                     when 'sunday' then 2
      9             when 'monday' then 3
     10             else 1
     11         end w_day
     12  from t
     13  /
    
    W_DAY
    ---------
    15-JUN-01
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    sliderrules wrote:
    Hi,

    I am using Oracle Database 11.2.1 and would like to return the last working day (DD/MM/YYYY).
    Last working day of week or month or year?

    >
    The working days run from Mon-Fri

    e.g. On Monday 18/06/2000 the value returned would be Fri 15/06/2000
    18/06/2000 is sunday
    SQL> select to_char(to_date('18/06/2000', 'dd/mm/yyyy'), 'day') from dual;
     
    TO_CHAR(T
    ---------
    sunday
    and 15/06/2000 is thursday

    Not sure what you are talking about.
  • sliderrules
    sliderrules Member Posts: 951
    I meant the working days of the week

    e.g. Mon 13/05/2013 would return 10/05/2013
    Tue 14/05/2013 would return 13/05/2013 etc
  • jeneesh
    jeneesh Member Posts: 7,168
    edited May 15, 2013 8:46AM Answer ✓
    sliderrules wrote:
    I meant the working days of the week

    e.g. Mon 13/05/2013 would return 10/05/2013
    Tue 14/05/2013 would return 13/05/2013 etc
    What about the query already provided?
    SQL> with t as
      2      (
      3      select to_date('13/05/2013 ','dd/mm/yyyy') dt
      4      from dual
      5      union all
      6      select to_date('14/05/2013 ','dd/mm/yyyy') dt
      7      from dual
      8      )
      9      select dt,dt -
     10             case to_char(dt,'fmday','nls_language=english')
     11                         when 'sunday' then 2
     12                 when 'monday' then 3
     13                else 1
     14            end last_w_day
     15     from t;
    
    DT        LAST_W_DA
    --------- ---------
    13-MAY-13 10-MAY-13
    14-MAY-13 13-MAY-13
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited May 15, 2013 8:47AM
    with data as (
    select to_date('18/06/2000','DD/MM/YYYY') d, to_date('16/06/2013','DD/MM/YYYY')  result from dual
    union all
    select to_date('19/06/2013','DD/MM/YYYY') d, to_date('18/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('20/06/2013','DD/MM/YYYY') d, to_date('19/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('21/06/2013','DD/MM/YYYY') d, to_date('20/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('22/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('23/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('24/06/2013','DD/MM/YYYY') d, to_date('21/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('25/06/2013','DD/MM/YYYY') d, to_date('24/06/2013','DD/MM/YYYY')  from dual
    union all
    select to_date('26/06/2013','DD/MM/YYYY') d, to_date('25/06/2013','DD/MM/YYYY') from dual
    )
     
    select
     d
    ,d-greatest(
    mod(to_char(d-1,'J'),7)+1-4
    ,1) last_wrk_day
    ,result
    from data
    
    D	LAST_WRK_DAY	RESULT
    18.06.2000	16.06.2000	16.06.2010
    19.06.2013	18.06.2013	18.06.2013
    20.06.2013	19.06.2013	19.06.2013
    21.06.2013	20.06.2013	20.06.2013
    22.06.2013	21.06.2013	21.06.2013
    23.06.2013	21.06.2013	21.06.2013
    24.06.2013	21.06.2013	21.06.2013
    25.06.2013	24.06.2013	24.06.2013
    26.06.2013	25.06.2013	25.06.2013
    Edited by: chris227 on 15.05.2013 05:47
  • kendenny
    kendenny Member Posts: 1,269
    edited May 15, 2013 8:45AM
    Use the next_day function. next_day(date,'DAY')
    SQL> select to_char(next_day(sysdate,'FRI'),'dd/mm/yyyy') from dual;
     
    TO_CHAR(NEXT_DAY(SYSDATE,'FRI'
    ------------------------------
    17/05/2013
    Of course if the date is already that day of the week then it will get the next one.
    SQL> select to_char(next_day(sysdate+2,'FRI'),'dd/mm/yyyy') from dual;
     
    TO_CHAR(NEXT_DAY(SYSDATE+2,'FR
    ------------------------------
    24/05/2013
    So you'll probably want to use date -1 in your calculation.
    Edit. Sorry I misunderstood the question.

    Edited by: kendenny on May 15, 2013 5:44 AM
This discussion has been closed.