Oracle and the awkward date formatting
Today I spent an hour trying to figure out why a trivial SQL query was not producing the expected results against an Oracle table. The query was something like:SELECT name, surname
FROM people
WHERE
working_date_begin <= current_date
and working_date_end >= current_date
;
The idea is: “get the people that is active on job”. What can be go wrong with this query? Well, since Oracle (
sqldeveloper and sqlplus, at least) report dates formatted as DD-MMM-YY, everything!
In fact, I was getting bited by something like 3-MAR-22, which at glance is not in the future with regard to current_date (which is the same day of writing, so 2026-03-23). From now on, I tried pretty much everything from checking sysdate to inspecting the column types to using trunc and range queries like current_date in working_date_begin and working_date_end, without a solution.
One strange thing I noted was that the working_date_begin, the lowest of the two, was correct with regard to current_date. Therefore I decided to check the dates one against the other with something like:
select *
from people
where
working_date_end < working_date_begin
;
The above query reported only a very few bogus records, so apparently the
working_date_end was greater than the other one, that in turn was greater than current_date, so what the hell was going on in here?
And then, I decided to show the dates using to_date, and to_date( working_date_end, 'YYYY-MM-DD' ) revelead I was hitting 2222-MAR-03, so the year was effectively in the future (as expected, clearly), but the date was truncated to appear ambigous.
Now, why this bited me? Because I’m used to PostgreSQL and other systems where dates are sane and in the correct explicit format with four digits per year!