Last date with time of the month

Question

Need your help to conclude the query to fetch last date time of the sysdate month.

select to_char(last_day(sysdate),'DD-Mon-YYYY HH24:MI:SS') from dual

it gives last date as expected, but I need time as 23:59:00 which is not possible thru above query.

Answer 1

You could use TRUNC on next day i.e. SYSDATE + 1, and then subtract 60 seconds i.e. 60/86400 to get the desired output.

SQL> SELECT to_char((trunc(last_day(sysdate)) +1) - 60/86400,'DD-Mon-YYYY HH24:MI:SS') dt
  2  FROM dual;

DT
--------------------
29-Feb-2016 23:59:00

SQL>

You could also use interval '1' minute or interval '60' second instead of 60/86400.