Oracle PL/SQL: how the SYSDATE is different from 'DD-MMM-YY'?

Question

I'm wondering how come SYSDATE is different from, let's say 28-APR-18 (assuming that SYSDATE is April, 28 of 2018).

I was debugging a little script I made, and got plenty of errors. After a while I managed to narrow it down to how the tables were filled (they were filled using the literal 'DD-MMM-YYYY' method and I was comparing against SYSDATE).

To understand, I wrote the following to see how each compares:

declare var1 DATE; var2 DATE;
BEGIN
   var1 := SYSDATE;
   var2 := '27-APR-18';

   if var1 = var2 then
   DBMS_OUTPUT.PUT_LINE('oh yeah');
   else DBMS_OUTPUT.PUT_LINE('WTF?'); 
       DBMS_OUTPUT.PUT_LINE(SYSDATE);DBMS_OUTPUT.PUT_LINE('27-APR-18');
   end if;
END;

If the above is ran, I get the following (which is getting me puzzled):

WTF?
27-APR-18
27-APR-18


PL/SQL procedure successfully completed.

Since they are both declared as DATE type, shouldn't they both be equal?

Thanks for your time and help!

Answer 1

In Oracle, a DATE value - despite the name - contains a time part as well. SYSDATE contains the current date and the current time (up to seconds).

The Oracle tools by default (stupidly) hide the time part of a DATE value. If you run:

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as sysdate
from dual;

you can see that.

So SYSDATE might be 2018-04-27 09:15:42 whereas the string (!) constant '27-APR-18' is silently converted to a DATE value at midnight: 2017-04-28 00:00:00

More details in the chapter Basic Elements of Oracle SQL in the manual

---

If you don't care about the time part, you can use trunc() to set the time to midnight, trunc(sysdate) yields 2018-04-27 00:00:00 (if today is 2018-04-27). Note that trunc() does not "remove" the time, it only sets it to 00:00:00

---

Unrelated, but:

You should never rely on implicit casting between strings and other non-character types which var2 := '27-APR-18' does - it would e.g. fail on my computer as my default NLS date format is different.

If you need a DATE value, then specify a proper date literal:

var2 := DATE '2018-04-27'; 

or

var2 := to_date('27-APR-18', 'dd-mon-rr');

or

var2 := to_date('27-APR-18 00:00:00', 'dd-mon-rr hh24:mi:ss');