learningKid learningKid - 4 months ago 8
SQL Question

Oracle SQL - Unable to get the data

I could see data in the table with

last_update_date
as
02-AUG-16


but when I query:

select * from table_test where last_update_date = '02-AUG-16'


No rows return.

What am I doing wrong here?

Answer

The dates in your table are not all at midnight, but your NLS_DATE_FORMAT setting isn't showing the time; and the implicit conversion you are doing is looking for exactly midnight.

If you do:

select to_char(last_update_date, 'YYYY-MM-DD HH24:MI:SS'
from table_test
where last_update_date >= date '2016-08-02'
and last_update_date < date '2016-08-03'

you will see the time portion of each value. That where clause looks for any value which is on or after midnight on the date you specify, and before midnight the next day (though you're unlikely to have any after today). If you just did:

select to_char(last_update_date, 'YYYY-MM-DD HH24:MI:SS'
from table_test
where last_update_date = date '2016-08-02'

or indeed, as you are implicitly doing:

where last_update_date = to_date('02-AUG-16', DD-MON-RR')

you won't see any matches, because none of the value found by the first query show the time as 00:00:00.

You should not rely on your session's NLS_DATE_FORMAT setting, either when querying or displaying data. It's better to always use to_char() for display - at the last possible moment - and to_date() for comparisons, specifying the format model you need, or using ANSI date literals as I've done above. (You shouldn't reply on other NLS sessing either; in this case you are also assuming your NLS_DATE_LANGUAGE is English to match AUG).