How to Compare Dates in Oracle SQL?

Answer

31-DEC-95 isn't a string, nor is 20-JUN-94. They're numbers with some extra stuff added on the end. This should be '31-DEC-95' or '20-JUN-94' - note the inverted commas, '. This will enable you to do a string comparison.

However, you're not doing a string comparison; you're doing a date comparison. You should transform your string into a date using the built-in to_date function.

select employee_id
  from employee
 where employee_date_hired > to_date('31-DEC-95','DD-MON-YY')

As a_horse_with_no_name noted in the comments, DEC, doesn't necessarily mean December. It depends on your NLS_DATE_LANGUAGE and NLS_DATE_FORMAT settings.

If you want to avoid this pitfall replace DEC with 12 and use the datetime format model MM to indicate this:

select employee_id
  from employee
 where employee_date_hired > to_date('31-12-95','DD-MM-YY')

Oracle does support ANSI date literals, though I prefer not to use them as I find the syntax less clear. When using a literal you must specify your date in the format YYYY-MM-DD and you cannot include a time portion.

select employee_id
  from employee
 where employee_date_hired > DATE '1995-12-31'