Skip to content

Comparing dates

Because datatype conversion and formatting is complex, working with dates can be a frustrating business. The usual rules apply though, and if you break the task down into smaller steps, you should find that you can deal with each step on its own quite easily. For example, imagine that you want to find the employees taken on before 1990. Using the Oracle HR schema, you would need to compare the value in the hire_date column of the employees table with a literal date value. You would therefore need to go through the following steps:

  1. Decide on the exact literal date for comparison
  2. Represent that date as a character string
  3. Convert that string into an internal date value
  4. Use the date value in the WHERE clause of a query

Going through these steps:

  1. ‘Before 1990’ actually means ‘before 1 Jan 1990’
  2. A valid representation of that date is ’01-JAN-1990’

  3. To convert the string into a date, use the TO_DATE function with the appropriate format:

    1
    TO_DATE('01-JAN-1990', 'DD-MON-YYYY')

  4. Construct a query:

    1
2
3
    SELECT employee_id
FROM   employees
WHERE  hire_date < TO_DATE('01-JAN-1990', 'DD-MON-YYYY');

Try this query in SQL*Plus.

Oracle does provide one shortcut: because DD-MON-YYYY is the default date format, the format string can be left out in this case:

1
2
3
    SELECT employee_id
    FROM   employees
    WHERE  hire_date < TO_DATE('01-JAN-1990');

Note the use of the standard arithmetic less than operator. Comparisons of this kind work intuitively enough. Another intuitive use of arithmetic operators is to add or subtract days from a given date value. The example below might be used to add 14 days to an invoice date to calculate the date that payment is due:

1
2
    SELECT SYSDATE + 14 AS “Due date”
    FROM   dual;

A date value can also be subtracted from a later date value to give the number of days between them; however, you must remember that the time component of the values will also be included. You will need to handle this explicitly in order to get an integer result.

Oracle provides many useful functions for manipulating date/time values, and going through each one explicitly would take a very long time. This is one of the most obvious examples of a situation where it is much more important to know where to find information about the topic when you need it rather than trying to memorise all of the details.

Referring back to the problem highlighted in the system time section, you can use the TRUNC function to remove the time element from SYSDATE:

1
2
3
    SELECT *
    FROM  date_tab
    WHERE TRUNC(date_col, 'DAY') = TRUNC(SYSDATE, 'DAY');

To finish this topic, read through the information about the following functions in the Oracle Reference:

  • ADD_MONTHS
  • EXTRACT
  • LAST_DAY
  • ROUND

Further reading

Data Type Comparison Rules

SQL Dates