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

Question

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'

Answer 1

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.

Answer 2

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>