-3
select * from accounts where sysdate - 100 < last_active_dt;

select * from accounts where sysdate - 100 <= last_active_dt;

Both of the queries returned the same result.

I was thinking that for the first query, it would not select records on the day of last_active_dt. For the 2nd query, it will select records on the day of last_active_dt.

But this does not seem to be the case as both returned the same results.

Is there any difference to it? And how can I see the difference?

last_active_dt column follows a date format DD-MON-YYYY
MT0
  • 143,790
  • 11
  • 59
  • 117
Z.H
  • 25
  • 1
  • 4

1 Answers1

2

last_active_dt column follows a date format DD-MON-YYYY

No, it doesn't (but it may look like it does in the user interface you are using).

A DATE data type always has year, month, day, hour, minute and second components and is stored (without any formatting) in 7 bytes. It is the user interface that applies a (default) format to the date and this may hide the time component from you.

You can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

To change the default date format used in SQL*Plus (and SQL Developer) to see the date with all the components.

Equally, SYSDATE is of a DATE data type and has year, month, day, hour, minute and second components so what you are probably intending is:

select * from accounts where TRUNC( sysdate ) - INTERVAL '100' DAY < last_active_dt;

This will get all the columns where the last_active_dt is after midnight of the day 100 days ago (even if it 1 second after). This will work if all your last_active_dt values have the times TRUNCated to exactly midnight 00:00:00 but not if the column as non-zero time components as you will still get values with times from 00:00:01 to 23:59:59 of the day 100 days ago.

If your last_active_dt has a non-zero time component then you want:

select * from accounts where TRUNC( sysdate ) - INTERVAL '99' DAY <= last_active_dt;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • As I go further into time, now this has all make sense with the current thing I am doing now. Thanks a lot! – Z.H Dec 20 '19 at 03:17