CODEWITHSUNDEEP
oracle-databasedateplsqldayofweek
Graffiti908
Graffiti908

Reputation: 63

How could i get the date by the day of week in oracle 11g PL/SQL

How can i find what date will be in the next MONDAY if i have a current date(sysdate) and a current day of week?

p.s please tell me how to get date by day of week NOT day of week by date.

Upvotes: 0

Views: 4171

Answers (3)

Marco Baldelli
Marco Baldelli

Reputation: 3728

You can use the next_day function:

select next_day(sysdate, 'MONDAY') from dual;

In case the national settings are not English we can have Oracle generate the localized translation of "Monday" and use it like this:

SQL> set serveroutput on
SQL> alter session set nls_date_format="YYYY-MM-DD";

Session altered.

SQL> alter session set nls_date_language=italian;

Session altered.

SQL> declare
  2     v_monday constant varchar2(100) := to_char(to_date('2013-09-30', 'yyyy-mm-dd'), 'day');
  3  begin
  4     dbms_output.put_line('v_monday = ' || v_monday);
  5     dbms_output.put_line('next monday will be ' || next_day(sysdate, v_monday));
  6  end;
  7  /
v_monday = lunedi
next monday will be 2013-09-30

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_language=spanish;

Session altered.

SQL> declare
  2     v_monday constant varchar2(100) := to_char(to_date('2013-09-30', 'yyyy-mm-dd'), 'day');
  3  begin
  4     dbms_output.put_line('v_monday = ' || v_monday);
  5     dbms_output.put_line('next monday will be ' || next_day(sysdate, v_monday));
  6  end;
  7  /
v_monday = lunes
next monday will be 2013-09-30

PL/SQL procedure successfully completed.

Upvotes: 2

Srini V
Srini V

Reputation: 11355

You can try this if you want to force giving current day and current date. Else you can make it more simpler with only date or with only month day.

WITH DATASET
    AS (SELECT
             'MON' AS CURRENT_DAY,
             '2013-06-26' AS CURENT_DATE
        FROM
             DUAL)
SELECT
      CASE
          WHEN CURRENT_DAY = 'MON'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '7' DAY
          WHEN CURRENT_DAY = 'TUE'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '6' DAY
          WHEN CURRENT_DAY = 'WED'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '5' DAY
          WHEN CURRENT_DAY = 'THU'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '4' DAY
          WHEN CURRENT_DAY = 'FRI'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '3' DAY
          WHEN CURRENT_DAY = 'SAT'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '2' DAY
          WHEN CURRENT_DAY = 'SUN'
          THEN
              TO_DATE ( CURENT_DATE,
                      'yyyy-mm-dd' )
              + INTERVAL '1' DAY
      END
          AS DATE_OF_NEXT_MON
FROM
      DATASET;

Upvotes: 1

HAL 9000
HAL 9000

Reputation: 3985

This depends on whether sunday is the first or last day of "your" week.

if your week starts with monday then use

select trunc(sysdate, 'iw') + 7 from dual;

if your week starts with sunday then use

select trunc(sysdate, 'w') + 8 from dual;

Upvotes: 0

Related Questions