CODEWITHSUNDEEP
sqloracle-database
kovashikawa
kovashikawa

Reputation: 1685

ORACLE SQL TRUNC DATE CHANGE

I can't undertand why does Oracle SQL do this:

SELECT TRUNC(TO_DATE('05-FEB-21 15:35:32', 'DD-MON-YY HH24:MI:SS'), 'DAY') AS BUG FROM DUAL;

Yields:

BUG
31-JAN-21

It is needed to enforce the TRUNC([...], 'DAY') so it can be filtered by day independently of hour.

Therefore, I needed the output as:

NONBUG
05-FEB-21

Can someone please help?

Thanks

Upvotes: 1

Views: 2042

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This is not a bug. Just a misuse of a date format.

If you want to trunc to the day, use 'DD' or just leave the argument off:

SELECT TRUNC(TO_DATE('05-FEB-21 15:35:32', 'DD-MON-YY HH24:MI:SS'), 'DD') AS BUG
FROM DUAL;

'DAY' (as explained in the documentation) is for the starting day of the week.

Upvotes: 4

Related Questions