How will you process date values stored in varchar fields?

Question

Your user wants a report sorted by date. This report is being created using C# and ODP.NET You do a sort on the Date column and the output is not exactly what the user expected.

Upon some closer inspection, you find that the date column is a varchar2 field. The values in the column are stored in DD-MON-YY "01-JAN-10" format.

How will you get it sorted by date?

My answer:

select TO_DATE(fakedatecolumn,'DD-MON-YY') from table ORDER BY TO_DATE(fakedatecolumn,'DD-MON-YY')

Is there something that is available in the front-end? I suppose DateTime.ParseExact will do the trick as well.

Answer 1

You answer is correct. Sorting should be done at the database level. Using DateTime.ParseExact would mean that all the rows need to be fetched at the client and then sorted which might not be very efficient. Once the results are fetched sorted from the database you could parse the string into a DateTime to eventually show it formatted in the user interface.

Answer 2

But if any of the values are EVER stored in the wrong format your query may crash, even if that wrong value is not logically part of the results (because Oracle can evaluate things out of order). To be really safe, you need to create a small PL/SQL function that catches conversion exceptions.

create or replace function safe_to_date(bad_data_type in varchar2) return date is
begin
  return to_date(bad_data_type, 'DD-MON-YYYY');
  --What happens if the format is wrong?  This just ignores it.
  exception when others then return null;
end;
/

Then you need to always use that function instead of to_date:

select safe_TO_DATE(fakedatecolumn,'DD-MON-YY')
from table
ORDER BY safe_TO_DATE(fakedatecolumn,'DD-MON-YY');

This sucks, but it's the price you pay for storing your data incorrectly.