How to return 14th day from month which was 2 months ago with determinated time in Oracle SQL?-CodePudding

How can I take the 14th day from month which was 2 months ago with time 23:59:59 in Oracle SQL ? I have query like below and I need answer something like below (similar style):

select to_char(to_date(LAST_DAY(ADD_MONTHS(sysdate, -2)), 'YY-MM-DD'), 'YY/MM/DD) from dual

Above query return: 21/12/31, nevertheless I need query like above which will return value: '21/12/14 23:59:59'

CodePudding user response：

You can use:

SELECT ADD_MONTHS(TRUNC(sysdate, 'MM'), -2)
           INTERVAL '13 23:59:59' DAY TO SECOND  AS day
FROM   DUAL

Which outputs:

DAY

2021-12-14 23:59:59

DAY
2021-12-14 23:59:59

If you want formatted with a specific format then use TO_CHAR:

SELECT TO_CHAR(
         ADD_MONTHS(TRUNC(sysdate, 'MM'), -2)
             INTERVAL '13 23:59:59' DAY TO SECOND,
         'YYYY-MM-DD HH24:MI:SS'
       ) AS day
FROM   DUAL

db<>fiddle here

CodePudding user response：

You can truncate to the start of the current month instead of using last_day, and then add 14 days and subtract one second - here using date arithmetic with fractions of days, but you could also use intervals (as @MTO shows):

select add_months(trunc(sysdate, 'MM'), -2)   14 - 1/86400 from dual

Which gives you a date value of 2021-12-14 23:59:59. You can then format that however you need.

db<>fiddle

Your current approach converts between dates and string, but relies on implicit conversion, which isn't a good idea. I've included that in the fiddle so you can see it breaking with a different session setting.

CodePudding user response：

If I understood you correctly, then

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.
    
SQL> select trunc(last_day(add_months(sysdate, -3))   15) - interval '1' second as result
  2  from dual;

RESULT
--------------------------------------------------------------------------------
14.12.2021 23:59:59

SQL>

Or, if it has to be a formatted string (above value is DATE):

SQL> select to_char(trunc(last_day(add_months(sysdate, -3))   15) - interval '1' second, 'yy/mm/dd hh24:mi:ss') as result
  2  from dual;

RESULT
--------------------------------------------------------------------------------
21/12/14 23:59:59

SQL>