Forum Stats

  • 3,872,467 Users
  • 2,266,427 Discussions
  • 7,911,214 Comments

Discussions

How to get the last non weekend day of a month

syparth-JavaNet
syparth-JavaNet Member Posts: 13
edited Feb 18, 2014 5:07AM in SQL & PL/SQL

Hello All,

Is the below query appropriate and best to use to get the last working day of the month ?

SELECT to_date('13.08.2014','dd.mm.yyyy') + MAX(RNUM) LastDay(nonweekend)
   FROM   (SELECT ROWNUM RNUM
          FROM   ALL_OBJECTS where rownum <=31)
  WHERE   ROWNUM <= 31 ANd
     TO_CHAR(to_date('13.08.2014','dd.mm.yyyy') + RNUM, 'DY','NLS_DATE_LANGUAGE=ENGLISH' ) NOT IN ('SAT' , 'SUN')
     and to_date('13.08.2014','dd.mm.yyyy') + RNUM <= last_day(to_date('13.08.2014','dd.mm.yyyy'))

I have to use this query in production.

Is this advisable to use ?

Tagged:
syparth-JavaNet

Best Answer

  • GregV
    GregV Member Posts: 3,106 Gold Crown
    Answer ✓

    You can change your query this way:

    WITH t AS (SELECT trunc(to_date('&calc_date', 'dd.mm.yyyy'), 'mm') + level - 1 calc_date
               FROM DUAL            
               CONNECT BY LEVEL <= last_day(to_date('&calc_date', 'dd.mm.yyyy')) - trunc(to_date('&calc_date', 'dd.mm.yyyy'), 'mm') + 1
              )
    select max(calc_date)
    from t           
    where to_char(calc_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH' ) NOT IN ('SAT' , 'SUN');
    

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    May be this

    SQL> with input_date
      2  as
      3  (
      4  select to_date('13.08.2014','dd.mm.yyyy') w_day
      5    from dual
      6  )
      7  , holiday
      8  as
      9  (
    10     select 1 i_day, 'saturday' h_day
    11       from dual
    12     union all
    13     select 2 i_day, 'sunday' h_day
    14       from dual
    15  )
    16  select last_day(w_day) - nvl(i_day, 0) last_w_day
    17    from input_date
    18    left
    19    join holiday
    20      on to_char(last_day(w_day), 'fmday') = h_day; LAST_W_DA
    ---------
    29-AUG-14 SQL>
  • GregV
    GregV Member Posts: 3,106 Gold Crown
    Answer ✓

    You can change your query this way:

    WITH t AS (SELECT trunc(to_date('&calc_date', 'dd.mm.yyyy'), 'mm') + level - 1 calc_date
               FROM DUAL            
               CONNECT BY LEVEL <= last_day(to_date('&calc_date', 'dd.mm.yyyy')) - trunc(to_date('&calc_date', 'dd.mm.yyyy'), 'mm') + 1
              )
    select max(calc_date)
    from t           
    where to_char(calc_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH' ) NOT IN ('SAT' , 'SUN');
    
  • KarK
    KarK Member Posts: 766
    edited Feb 18, 2014 5:07AM

    Hi,

    Something like below:

    SELECT
      CASE
        WHEN TO_CHAR(last_day(to_date('12-NOV-2013','DD-MON-YYYY')),'DY') IN ('MON','TUE','WED','THU','FRI')
        THEN last_day(to_date('12-NOV-2013','DD-MON-YYYY'))
        ELSE
          CASE
            WHEN TO_CHAR(last_day(to_date('12-NOV-2013','DD-MON-YYYY')),'DY')='SAT'
            THEN last_day(to_date('12-NOV-2013','DD-MON-YYYY'))-1
            ELSE last_day(to_date('12-NOV-2013','DD-MON-YYYY'))-2
          END
      END LASTDAY
       FROM dual;
    
    OUTPUT:
    
    LASTDAY
    ---------
    29-NOV-13
    
    
    Pass your input string:
    
     SELECT
      CASE
        WHEN TO_CHAR(last_day(to_date('&INPUT_DATE','DD-MON-YYYY')),'DY') IN ('MON','TUE','WED','THU','FRI')
        THEN last_day(to_date('&INPUT_DATE','DD-MON-YYYY'))
        ELSE
          CASE
            WHEN TO_CHAR(last_day(to_date('&INPUT_DATE','DD-MON-YYYY')),'DY')='SAT'
            THEN last_day(to_date('&INPUT_DATE','DD-MON-YYYY'))-1
            ELSE last_day(to_date('&INPUT_DATE','DD-MON-YYYY'))-2
          END
      END LASTDAY
       FROM dual;
    
    
    
    KarK
  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy
    edited Feb 18, 2014 5:10AM

    Try this?

    SELECT CASE WHEN TO_CHAR(LAST_DAY(w_day),'DY','NLS_DATE_LANGUAGE = ENGLISH') = 'SAT' THEN LAST_DAY(w_day) - 1
                WHEN TO_CHAR(LAST_DAY(w_day),'DY','NLS_DATE_LANGUAGE = ENGLISH') = 'SUN' THEN LAST_DAY(w_day) - 2
           ELSE LAST_DAY(w_day)
           END AS lastday
    FROM(SELECT TO_DATE('13.08.2014','dd.mm.yyyy') w_day
         FROM dual);
    
    
    AnnEdmund
  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy
    edited Feb 18, 2014 5:06AM
    SELECT CASE WHEN(TO_CHAR(LAST_DAY(TO_DATE('13.08.2014','dd.mm.yyyy')),'DY')='SAT') THEN LAST_DAY(TO_DATE('13.08.2014','dd.mm.yyyy'))-1
                WHEN(TO_CHAR(LAST_DAY(TO_DATE('13.08.2014','dd.mm.yyyy')),'DY')='SUN') THEN LAST_DAY(TO_DATE('13.08.2014','dd.mm.yyyy'))-2
                ELSE LAST_DAY(TO_DATE('13.08.2014','dd.mm.yyyy')) END AS DAYS
    FROM DUAL;
    
    output:
    
    29-AUG-14
    
  • bencol
    bencol Member Posts: 894 Bronze Badge
    edited Feb 18, 2014 5:08AM


    Why not use the last_day() function and then check what its value is then take off the appropriate number of days:

    with t as (select add_months(sysdate,level) d

                from   dual

                connect by level <=12

               )

    -- end of test data

    select d

          ,case to_char(last_day(d),'D','NLS_DATE_LANGUAGE=ENGLISH') 

             when '6' then

               last_day(d) - 1

             when '7' then

               last_day(d) - 2

             else

               last_day(d)

           end last_working_day

          ,last_day(d) last_day

    from t;

    D                    LAST_WORKING_DAY     LAST_DAY

    ____________________ ____________________ ____________________

    Tuesday   18/03/2014 Monday    31/03/2014 Monday    31/03/2014

    Friday    18/04/2014 Wednesday 30/04/2014 Wednesday 30/04/2014

    Sunday    18/05/2014 Friday    30/05/2014 Saturday  31/05/2014

    Wednesday 18/06/2014 Monday    30/06/2014 Monday    30/06/2014

    Friday    18/07/2014 Thursday  31/07/2014 Thursday  31/07/2014

    Monday    18/08/2014 Friday    29/08/2014 Sunday    31/08/2014

    Thursday  18/09/2014 Tuesday   30/09/2014 Tuesday   30/09/2014

    Saturday  18/10/2014 Friday    31/10/2014 Friday    31/10/2014

    Tuesday   18/11/2014 Friday    28/11/2014 Sunday    30/11/2014

    Thursday  18/12/2014 Wednesday 31/12/2014 Wednesday 31/12/2014

    Sunday    18/01/2015 Friday    30/01/2015 Saturday  31/01/2015

    Wednesday 18/02/2015 Friday    27/02/2015 Saturday  28/02/2015

    Or in PL/SQL:


    create or replace function LastWorkDay (d in date)
    return date
    as
    begin
      case to_char(last_day(d),'D','NLS_DATE_LANGUAGE=ENGLISH') 
        when '6' then
          return last_day(d) - 1;
        when '7' then
          return last_day(d) - 2;
        else
          return last_day(d);
      end case;
    end;
    /

    sho err

    with t as (select add_months(sysdate,level) d
                from   dual
                connect by level <=12
               )
    select d
          ,LastWorkDay(d)  last_working_day
          ,last_day(d) last_day
    from   t;

    For the same results.

    syparth-JavaNetbencol
This discussion has been closed.