Oracle SQL where clause does not work for some dates

Below query works fine with the recent dates (for e.g. if the date is 20-JUN-16, 30-SEP-19). But if I query with date value set to 29-MAR-80, 01-JAN-94 it does not return any result. Wondering what could be the reason. Am not allowed to change the existing year format to YYYY. We are on Oracle 19c.

select * from V_IV_MUC where TRUNC(VALUE_TIMESTAMP) = '01-FEB-80';

Answer

Here, your date '01-FEB-80' will be converted to '01-FEB-1980', if your two digit year format is RR(in NLS_DATE_FORMAT) and it will be converted to '01-FEB-2080' if your date format is set to YY(in NLS_DATE_FORMAT).

• YY — convert the year to current century year – 80 --> 2080
• RR — convert the year to the year based on current year (1950-2049) so 80 --> 1980.

You can use any of the following two queries to avoid any such dependencies on database settings.

-- use date literal
select * from V_IV_MUC where TRUNC(VALUE_TIMESTAMP) = date '1980-02-01';

-- use date string with format
select * from V_IV_MUC where TRUNC(VALUE_TIMESTAMP) = to_date('01-FEB-80','dd-mon-rr');