Internals of SYSDATE and Date Comparisons in Oracle

1. What is `SYSDATE`?

SYSDATE returns the current date and time from the system clock of the database server, not the client.

Data Type:

Oracle DATE type

Stored internally as a 7-byte binary:


Byte 1: Century + 100
Byte 2: Year + 100
Byte 3: Month
Byte 4: Day
Byte 5: Hour + 1
Byte 6: Minute + 1
Byte 7: Second + 1

Example:

Let's say SYSDATE = 13-MAY-25 14:37:29. Internally it's stored as:


Bytes: 120 125 05 13 15 38 30
         |   |  |  |  |  |  |
        20  25 05 13 14:37:29 +1 each

2. Key Concepts

A. `DATE` Data Type Always Has Time

Even if you see:


13-MAY-25

It is actually:


13-MAY-25 00:00:00 (or with full time component)

B. Display vs Storage

Oracle may display a date as DD-MON-YY depending on NLS_DATE_FORMAT, but it still contains time unless explicitly truncated.

3. Comparisons: Real Examples

Let’s create a test table:


CREATE TABLE test_dates (
  id NUMBER,
  date_val DATE
);

Insert data with different time portions:


INSERT INTO test_dates VALUES 
(1, TO_DATE('13-MAY-25 14:00:00', 'DD-MON-RR HH24:MI:SS'));
INSERT INTO test_dates VALUES 
(2, TO_DATE('13-MAY-25 00:00:00', 'DD-MON-RR HH24:MI:SS'));

Now try:


SELECT * FROM test_dates WHERE date_val = TO_DATE('13-MAY-25', 'DD-MON-RR');

This only matches row 2, not row 1, because it assumes time 00:00:00.

4. Safe Comparisons

A. Match just the date:


WHERE TRUNC(date_val) = TRUNC(SYSDATE)

TRUNC removes time.
Internally, Oracle compares only the date part, ignoring hours, minutes, seconds.

B. Match all values from today:


WHERE date_val BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE + 1) - 1/86400

This includes 23:59:59 on the same day.

5. Why `SYSDATE + 1` May Show Only Date


SELECT SYSDATE, SYSDATE + 1 FROM DUAL;

You might see:


SYSDATE           SYSDATE+1
13-MAY-25 14:37   14-MAY-25

Why?

It’s because:

SYSDATE + 1 adds exactly 1 day (24 hours).
If NLS format only shows date (DD-MON-YY), time is hidden.

But the internal structure is still:


14-MAY-25 14:37:29

6. Execution and Optimizer Notes

Oracle compares DATE fields byte-by-byte.
When comparing SYSDATE = column, it checks all 7 bytes.
If you use TRUNC(column) = TRUNC(SYSDATE), Oracle can’t use index on column unless it's a function-based index on TRUNC(column).

7. Function-Based Index for Performance

To avoid full scan:


CREATE INDEX idx_trunc_date_val ON test_dates (TRUNC(date_val));

Now:


WHERE TRUNC(date_val) = TRUNC(SYSDATE)

can use the index.

8. Common Mistakes

Mistake	What Happens
`WHERE date_col = SYSDATE`	Fails unless exact time matches
`WHERE date_col = TO_DATE('13-MAY-25')`	Assumes time = 00:00:00
Using `TRUNC()` on indexed column	Index may be ignored unless function-based

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Internals of SYSDATE and Date Comparisons in Oracle