Oracle - LAST_DAY and TRUNC - order of execution

user_4174701 0
Viewed 114

I am getting unexpected result when using combination of LAST_DAY and TRUNC:

select LAST_DAY(TRUNC(sysdate, 'DAY')) from dual;
--31-JUL-2021 00:00:00

select TRUNC(LAST_DAY(sysdate), 'DAY')from dual;
--26-JUL-2021 00:00:00

Why these calls does not return same results? I mean, what is the difference between:

  1. I want today to be truncated (get rid of hours)
  2. Then get last day of the month

And

  1. I want last day of the month (with hour)
  2. And then get rid of the hour part
oracle19c plsql datetime oracle
1 Answers

TRUNC(datestamp, 'DAY') gets you the first day of the week containing the datestamp. Weird but true.

If you want the day of the datestamp, simply use TRUNC(datestamp).

Oracle datestamps are floating-point numbers under the hood.

user_2056080
Related