Get particular date from dual - oracle-CodePudding

i want create oracle query using dual to pickup the correct business day

condition: if 1st day of the month falls on sunday or monday then it need to choose tuesday date

CodePudding user response：

So, if 1st day of month is Sunday or Monday, you want to return the next Tuesday. For all other days, you want to return that day.

Just to set the environment (you don't have to do that):

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy, dy';

Session altered.

Asterisk marks months which reflect the requirement.

SQL> with
  2  temp (sys_date) as
  3    -- it simulates 15th day of each month in 2021; something what SYSDATE would return
  4    (select add_months(date '2021-01-15', level - 1)
  5     from dual
  6     connect by level <= 12),
  7  day (sys_date, day) as
  8    -- day name
  9    (select sys_date,
 10            to_char(trunc(sys_date, 'mm'), 'dy')
 11     from temp
 12    )
 13  select trunc(t.sys_date, 'mm') first_day_of_month,
 14         --
 15         case when d.day in ('sun', 'mon') then next_day(trunc(t.sys_date, 'mm'), 'tue')
 16              else trunc(t.sys_date, 'mm')
 17         end result
 18  from day d join temp t on trunc(d.sys_date, 'mm') = trunc(t.sys_date, 'mm');

FIRST_DAY_OF_MO RESULT
--------------- --------------------
01.01.2021, fri 01.01.2021, fri
01.02.2021, mon 02.02.2021, tue  *
01.03.2021, mon 02.03.2021, tue  *
01.04.2021, thu 01.04.2021, thu
01.05.2021, sat 01.05.2021, sat
01.06.2021, tue 01.06.2021, tue
01.07.2021, thu 01.07.2021, thu
01.08.2021, sun 03.08.2021, tue  *
01.09.2021, wed 01.09.2021, wed
01.10.2021, fri 01.10.2021, fri
01.11.2021, mon 02.11.2021, tue  *
01.12.2021, wed 01.12.2021, wed

12 rows selected.

SQL>

In reality, you'd need a simpler version:

SQL> select case when to_char(trunc(sysdate, 'mm'), 'dy') in ('sun', 'mon') then next_day(trunc(sysdate, 'mm'), 'tue')
  2              else trunc(sysdate, 'mm')
  3         end result
  4  from dual;

RESULT
--------------------
01.10.2021, fri

SQL>

CodePudding user response：

if 1st day of the month falls on sunday or monday then it need to choose tuesday date

As I understand it, if the first day of the current month is either Sunday or Monday then you want to return Tuesday of the current week; otherwise return the current day:

SELECT CASE
       WHEN TRUNC(SYSDATE, 'MM') - TRUNC(TRUNC(SYSDATE, 'MM'), 'IW')
            IN (0, 6)
       THEN TRUNC(SYSDATE) - (TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW'))   1
       ELSE TRUNC(SYSDATE)
       END
FROM   DUAL;

Note: this will work in any territory or language.