Comparing Dates in Oracle SQL

date-comparison, oracle, sql

Solution

`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 single quote, `'`. 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. Either by using the built-in `TO_DATE()` function, or a date literal.

TO_DATE()

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

This method has a few unnecessary pitfalls

- 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. To ensure that your comparison will work in any locale you can use the datetime format model `MM` instead

- The year '95 is inexact. You know you mean 1995, but what if it was '50, is that 1950 or 2050? It's always best to be explicit

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

Date literals

A date literal is part of the ANSI standard, which means you don't have to use an Oracle specific function. When using a literal you must specify your date in the format `YYYY-MM-DD` and you cannot include a time element.

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

Remember that the Oracle date datatype includes a time element, so the date without a time portion is equivalent to `1995-12-31 00:00:00`.

If you want to include a time portion then you'd have to use a timestamp literal, which takes the format `YYYY-MM-DD HH24:MI:SS[.FF0-9]`

select employee_id
  from employee
 where employee_date_hired > timestamp '1995-12-31 12:31:02'

Further information

`NLS_DATE_LANGUAGE` is derived from `NLS_LANGUAGE` and `NLS_DATE_FORMAT` is derived from `NLS_TERRITORY`. These are set when you initially created the database but they can be altered by changing your initialization parameters file - only if really required - or at the session level by using the `ALTER SESSION` syntax. For instance:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

This means:

- `DD` numeric day of the month, 1 - 31

- `MM` numeric month of the year, 01 - 12 ( January is 01 )

- `YYYY` 4 digit year - in my opinion this is always better than a 2 digit year `YY` as there is no confusion with what century you're referring to.

- `HH24` hour of the day, 0 - 23

- `MI` minute of the hour, 0 - 59

- `SS` second of the minute, 0-59

You can find out your current language and date language settings by querying `V$NLS_PARAMETERSs` and the full gamut of valid values by querying `V$NLS_VALID_VALUES`.

Further reading

- Format models

Incidentally, if you want the `count(*)` you need to group by `employee_id`

select employee_id, count(*)
  from employee
 where employee_date_hired > date '1995-12-31'
 group by employee_id

This gives you the count per `employee_id`.

Problem

Original source