DATE Format SQL for Dates Stored as Strings

It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to represent dates rules out your ability to build upon features like range interval partitioning. Yes – ideally you should never have to convert VARCHAR2 to date.

It is equally well-acknowledged that we do not live in an ideal world. In some situations, we do not control the database design; we have to live with what exists and provide a solution to the problem.

This article addresses one such problem — how to compare two dates when they are stored as strings with different DATE formats.

Consider this typical scenario:

Two tables — ORDERS and PAYMENTS — contain date values stored in VARCHAR2 columns.

ORDERS table stores ORDER_DATE in DD/MM/YYYY format
e.g. 12/03/2017.
PAYMENTS table stores PAYMENT_DATE in YYYY-MON-DD format
e.g. 2017-MAR-12.

How will you verify if ORDER_DATE is the same as PAYMENT_DATE?

The Problematic DATE Data

ORDERS:

SQL> select *
  2  from orders;

  ORDER_ID ORDER_DATE
---------- ------------
         1 12/03/2017
         2 14/03/2017
         3 04/05/2017

PAYMENTS:

SQL> select *
  2  from payments;

  ORDER_ID PAYMENT_ID PAYMENT_DATE
---------- ---------- ------------
         1        101 2017-MAR-12
         2        201 2017-MAR-14
         2        202 2017-APR-16
         3        301 2017-MAY-06

A visual check tells you that ORDER_DATE is the same as PAYMENT_DATE in two cases:

(ORDER_ID = 1, PAYMENT_ID = 101)
(ORDER_ID = 2, PAYMENT_ID = 201)

A SQL with a straight comparison of the values will not detect this match, though — and rightly so. The SQL attempts a simple text comparison and fails to find a match.

SQL> -- Compare date values in VARCHAR2 columns
SQL> -- without formatting. Result unreliable!
SQL> select o.order_id
  2       , o.order_date
  3       , p.payment_id
  4       , p.payment_date
  5  from orders o
  6  inner join payments p
  7  on
  8  o.order_id = p.order_id
  9  where o.order_date = p.payment_date;

no rows selected

Convert both VARCHAR2 columns to DATE

Use Oracle’s in-built TO_DATE function to convert the string to DATE on both sides of the comparison.

TO_DATE takes three inputs: one mandatory, one recommended and one optional.

1. [mandatory] the string to be converted to DATE
2. [recommended*] the DATE format in which the string should be read
3. [optional] the language of the string to be converted to DATE

In the example of ORDERS and PAYMENTS tables, the date columns would be cast as:

to_date(o.order_date, ‘YYYY/MM/DD’)
to_date(p.payment_date, ‘DD-MON-YYYY’)

DATE format SQL for dates stored as strings:

-- Compare date values in VARCHAR2
--  columns after casting+formatting
-- both sides as DATES
select o.order_id
     , o.order_date
     , p.payment_id
     , p.payment_date
from orders o
inner join payments p
on
o.order_id = p.order_id
where to_date(o.order_date, 'DD/MM/YYYY') 
    = to_date(p.payment_date, 'YYYY-MON-DD');

When run:

SQL> -- Compare date values in VARCHAR2
SQL> -- columns after casting+formatting
SQL> -- both sides as DATES
SQL> select o.order_id
  2       , o.order_date
  3       , p.payment_id
  4       , p.payment_date
  5  from orders o
  6  inner join payments p
  7  on
  8  o.order_id = p.order_id
  9  where to_date(o.order_date, 'DD/MM/YYYY')
 10      = to_date(p.payment_date, 'YYYY-MON-DD');

  ORDER_ID ORDER_DATE   PAYMENT_ID PAYMENT_DATE
---------- ------------ ---------- ------------
         1 12/03/2017          101 2017-MAR-12
         2 14/03/2017          201 2017-MAR-14

…and you get what you wanted.

Caution!

*It is a wise practice to include the date format in TO_DATE though the parameter is technically optional. If the date format is unspecified, TO_DATE works only if the string to be converted uses the same format as specified in the database’s NLS_TERRITORY or NLS_DATE_FORMAT parameters.

Omitting the date format can also cause problems during code migration across databases or if the NLS parameter values are modified.

A mistake in the date format provided to TO_DATE, or the presence unexpected/invalid date values in the string, leads to ORA errors.

Example: ORDER_DATE is in DD/MM/YYYY format and let’s say the format provided in the SQL is YYYY/MM/DD. This will cause the error ORA-01830.

SQL> -- Error in date format 
SQL> -- provided to TO_DATE
SQL> select o.order_id
  2       , o.order_date
  3       , p.payment_id
  4       , p.payment_date
  5  from orders o
  6  join payments p
  7  on
  8  o.order_id = p.order_id
  9  where to_date(o.order_date, 'YYYY/MM/DD')
 10      = to_date(p.payment_date, 'DD-MON-YYYY');
where to_date(o.order_date, 'YYYY/MM/DD')
              *
ERROR at line 9:
ORA-01830: date format picture ends before converting entire input string