CODEWITHSUNDEEP
sqloracle-database
user9632326
user9632326

Reputation:

last day/time of month

How do I get the last day of the month to be 11:59:59 pm? Right now it is showing 05/31/2019 12:00 AM. If I just do sysdate it just shows the time I ran it. Thanks. 

LAST_DAY(TRUNC(SYSDATE,'MM'))

Upvotes: 1

Views: 1697

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

Try adding one day to the last day of the current month, then subtracting one minute:

SELECT
    LAST_DAY(TRUNC(SYSDATE,'MM')) + INTERVAL '1' DAY - INTERVAL '1' SECOND
FROM dual;

Or, if you want to also see the time component, use CURRENT_TIMESTAMP in place of SYSDATE:

SELECT
    LAST_DAY(TRUNC(CURRENT_TIMESTAMP,'MM')) + INTERVAL '1' DAY - INTERVAL '1' SECOND
FROM dual;

This outputted:

30.06.2019 23:59:59

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I would use:

SELECT TRUNC(SYSDATE, 'MM') + INTERVAL '1' MONTH - INTERVAL '1' SECOND
FROM dual;

(This has one less step than Tim's solution.)

Or in the older-fashioned method:

SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1/(24*60*60)

If you convert this to a timestamp, you will see the time value:

SELECT CAST(TRUNC(sysdate, 'MM') + INTERVAL '1' MONTH - INTERVAL '1' SECOND as TIMESTAMP)
FROM dual;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

The oldfashioned way would be to subtract a second (that's what 1 / (24*60*60) represents as there are 24 hours in a day, 60 minutes in an hour and 60 seconds in a minute) from the first day of the next month (and that's what trunc(add_months(sysdate, 1)) does):

SQL> select sysdate today,
  2         trunc(add_months(sysdate, 1)) - 1/(24*60*60) result
  3  from dual;

TODAY                  RESULT
---------------------- ----------------------
06/01/2019 07:52:40 AM 06/30/2019 11:59:59 PM

SQL>

Upvotes: 0

Related Questions