CODEWITHSUNDEEP
sqloracle-databasedatetimedate-arithmetic
user2469253
user2469253

Reputation: 89

Last date with time of the month

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.

Upvotes: 1

Views: 1220

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191570

If you just want it for display for some reason you can hard-code the time into the format mask:

select to_char(last_day(sysdate), 'DD-Mon-YYYY "23:59:00"') from dual;

But you probably really want it as a date object, in which case you can add 23 hours and 59 minutes to the truncated (midnight) date, wchi is 1439 of the 1440 minutes in a day:

select to_char(trunc(last_day(sysdate)) + 1439/1440, 'DD-Mon-YYYY HH24:MI:SS')
from dual;

Or you can go to the next day and remove a minute, either with fractional days or with intervals:

select to_char(trunc(last_day(sysdate)) + interval '1' day - interval '1' minute,
  'DD-Mon-YYYY HH24:MI:SS') from dual;

Generally if you're working with time periods you want to include up to 23:59:59, which you can also do with any of those methods, but as Damien_The_Unbeliever said in a comment, it's easier to compare against the start of the next period (e.g. < add_months(trunc(sysdate, 'MM'), 1). It's easy to accidentally miss part of a day by not taking the time into account properly, particularly if you actually have a timestamp rather than a date.

Upvotes: 0

Related Questions