There is a table in Oracle 19c with a DATE abc column. A row with value '2000-01-01' does not get picked up with query

select abc from t where abc <= DATE '2000-01-01'

The row does surface if I modify the query as

select abc from t where abc < DATE '2000-01-01'   interval '1' day

The displayed value (in DBeaver, VSCode Oracle Dev Tools, Oracle SQL Developer) is always '2000-01-01'.

CodePudding user response：

Issue

Oracle stores also time portion of inserted/copied data and while it doesn't always show, it affects queries regardless of output data.

You can notice it with query:

select to_char(abc, 'YYYY-MM-DD hh24:MI:ss') from t 
where trunc(abc) <= date '2000-01-01'

Mitigation

• use the comparable date (you're searching for) as variable and add interval '1' day to it. Notice to adjust your comparison limits, use < instead of <= to not accidentally find items from next day with 00:00:00 time.
• don't use trunc(abc) or to_char(abc ..), it will likely wreck the index performance`
• add ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; to your sql editor startup script.

CodePudding user response：

In Oracle, a DATE is a binary data type that consists of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 bytes and it is NEVER stored in any particular (human-readable) format.

The displayed value (in DBeaver, VSCode Oracle Dev Tools, Oracle SQL Developer) is always '2000-01-01'.

What you are seeing is the client application receiving the binary DATE value and trying to be helpful and displaying the binary value as a string; however, the client application's default format is YYYY-MM-DD and it is not showing the time component of the date. This does not mean that the time component does not exist, it only means that it is not displayed.

What you need to do is to go into the preferences and change how your client application formats dates.

In SQL Developer, you can use:

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

Which will set the format for your current session. Or you can go into Tool > Settings > Database > NLS and in set the "Date Format" field to YYYY-MM-DD HH24:MI:SS which will set the preference for the current session and any future session that you create from that client.

You can do something similar and change the settings in most (all) client applications.

select abc from t where abc <= DATE '2000-01-01'

Is the equivalent of:

select abc from t where abc <= TIMESTAMP '2000-01-01 00:00:00'

It will not match rows where abc is between 2000-01-01 00:00:01 and 2000-01-01 23:59:59 but the client application is not displaying the time component so you cannot see that the rows have a non-midnight time component and should not be matched.

If you do:

select abc from t where abc < DATE '2000-01-01'   interval '1' day

Then it will match those rows because it will match the entire 24-hour period for the day.