sqloracle-databaseoracle-sqldeveloper

Comparisons using EXTRACT YEAR not working

I have this query:

SELECT * 
FROM customer 
WHERE EXTRACT(YEAR FROM C_BIRTHDATE) < 70 
   OR EXTRACT(YEAR FROM C_BIRTHDATE) > 79;

I'm pretty new to SQL. I'm having trouble finding why this might not be working, as it seems a pretty simple thing to look up.

These are my results. As you see, the results returned include entries from 1973, but I'm specifically trying to exclude all entries in the 1970-1980 range (this is test data, so sharing it represents no security issues):

SQL showing entries from 1973, despite me specifically excluding them in the code.

I am pretty new to SQL, so I might be missing something obvious. About an hour of research hasn't really yielded results though, so I figured asking people more intimately familiar with the language might be productive.

Solution

  • Your client is showing you the dates as DD-MM-RR format. They are stored in an internal representation, not with any human-readable format. The year isn't really 70, it's (presumably) 1970.

    So you would need to use the full year number:

    SELECT * FROM customer
WHERE EXTRACT(YEAR FROM C_BIRTHDATE) < 1970
OR EXTRACT(YEAR FROM C_BIRTHDATE) > 1979;

    Or more simply (and possibly efficiently):

    SELECT * FROM customer
WHERE C_BIRTHDATE < DATE '1970-01-01'
OR C_BIRTHDATE >= DATE '1980-01-01';