7 Replies Latest reply: Dec 11, 2012 6:58 AM by Paul Horth RSS

    How to get last day of week ?

    574641
      Hi All,

      How to get the last day of the week in Oracle?

      My requirement is that I need to run a process for a weekly range. So in the current month I need to Run a process from 1st Jan 2009 to 31st Jan 2009 and get the aggregation grouped by week. (Start of week is sunday and end of week is Saturday).
      I need to set the cut-off date looking into last date of week. ie if 31st falls on a Wednesday then I should not process from that sunday onwards(28th onwards ) since they end of week falls in Next Month.

      I need to find the cut-off date. do we have something like select last_day_of_week(input date) from dual? ( Being a little greedy here :-) ) .......

      Thanks and Regards,
      Saffron
        • 1. Re: How to get last day of week ?
          667579
          select trunc(sysdate, 'DAY')+6 from dual;
          
          TRUNC(SYSDATE,'DAY')+6    
          ------------------------- 
          24-JAN-09                 
          
          1 rows selected
          trunc to "day" is the first day of the week, so add 6 for the last day.
          • 2. Re: How to get last day of week ?
            OrionNet
            Hello,

            This will work for you
             select next_day(sysdate,'saturday') from dual;
            Regards

            Edited by: OrionNet on Jan 20, 2009 4:13 PM
            • 3. Re: How to get last day of week ?
              Frank Kulash
              Hi,
              OrionNet wrote:
              Hello,

              This will work for you
              select next_day(sysdate,'saturday') from dual;
              Regards

              Edited by: OrionNet on Jan 20, 2009 4:13 PM
              Be careful! That returns the Saturday after today. If today happens to be a Saturday, (e.g., Jan. 17, 2009) that will be the end of the following week (Jan. 24).
              NEXT_DAY (dt - 1, 'Saturday')
              or
              NEXT_DAY (dt, 'Sunday') - 1
              will return the Saturday on or after dt.
              • 4. Re: How to get last day of week ?
                574641
                WOWWWWWW.... so much of responses within this time... This forum really saves so much of junk codes which I normally do, by providing optimal solution...

                Thanks to all you gurus...

                regards,
                Saffron
                • 5. Re: How to get last day of week ?
                  574641
                  Hi All,

                  Based on the input for all of you, this is the simple block I have created:
                  This just checks if the end of the week for the processing end date falls in next month , then dont use that week.
                  DECLARE
                     lv_strt_dt           DATE := TO_DATE ('01-Feb-2009');
                     lv_end_dt            DATE := TO_DATE ('28-Feb-2009');
                     ld_start_cutoff_date DATE;
                     ld_end_cutoff_date   DATE;
                  BEGIN
                  
                     ld_start_cutoff_date := TRUNC (lv_strt_dt, 'DAY');
                     
                     IF TRUNC (TRUNC (lv_end_dt, 'DAY') + 6, 'MM') < > TRUNC (lv_end_dt, 'MM')
                     THEN
                        ld_end_cutoff_date := (TRUNC (lv_end_dt, 'DAY') - 1);
                     ELSE
                        ld_end_cutoff_date := (TRUNC (lv_end_dt, 'DAY') + 6);
                     END IF;
                  
                     DBMS_OUTPUT.put_line (ld_start_cutoff_date||','||ld_end_cutoff_date);
                  END;
                  Thanks and Regards,
                  Saff

                  Edited by: saffron on Jan 20, 2009 1:54 PM

                  Edited by: saffron on Jan 20, 2009 1:56 PM
                  • 6. Re: How to get last day of week ?
                    979242
                    if i want monday as my week start and sunday as my end of the week,
                    then
                    this query will fail

                    can you suggest me the solution for that
                    • 7. Re: How to get last day of week ?
                      Paul  Horth
                      976239 wrote:
                      if i want monday as my week start and sunday as my end of the week,
                      then
                      this query will fail

                      can you suggest me the solution for that
                      Do not resurrect 3 year old posts.

                      Start one of your own.