Titoy Titoy - 6 months ago 24
SQL Question

MIN on a date field

I am using an Oracle database.

Is it possible to apply a

MIN
to a date field, as follows:

MIN(TO_DATE(Person.birthday, 'DD-Mon-YY'))

Answer

This is a simple question but there's a catch to it.

Is it possible to apply MIN to date column ?

  • Yes you can.

Is this the correct way MIN(TO_DATE(Person.birthday, 'DD-Mon-YY')) ?

  • No. You should use MIN(Person.birthday) If the column is already DATE type, you should not use TO_DATE to convert it again as ORACLE converts it implicitly.

Here's an example why -

DATA -

+-------+--------+-----------+------+-------------+------+------+--------+
| EMPNO | ENAME  |    JOB    | MGR  |  HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+-------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 17/Dec/1980 |  800 |      |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 20/Feb/1981 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 22/Feb/1981 | 1250 |  500 |     30 |
+-------+--------+-----------+------+-------------+------+------+--------+

QUERY 1

select MIN(TO_DATE(hiredate, 'DD-Mon-YY')) from emp;

RESULT 1

17/12/2080

QUERY 2

select MIN(hiredate) from emp;

RESULT 2

17/12/1980

As you can see the century is messed up when you use TO_DATE function in QUERY 1. However, the the result is as expected in QUERY 2

If you necessarily have to use TO_DATE function, I would suggest you to use DD-Mon-RR format as it takes care of the century mismatch. This format was created when the problem for year Y2K (the Millennium bug) came up. However, I still wouldn't advise to go for it.

EDIT 1:

I am not sure how Person.birthday is a valid column name. Can anyone enlighten me on this ?