Nimbocrux Nimbocrux - 2 months ago 14
SQL Question

Oracle SQL DATE datatype returns latest date as 31/12/99 despite younger records

This is hurting me.
Oracle RDMS using SQL Devloper v. 4
I have records in the table

EMP|DATE_STARTED
JOE|11/08/06
BOB|11/08/14
MAY|31/12/99


DATE_STARTED is DATE datatype.

My query below returns 31/12/99 as the latest date, but the table has later records (2006 and 2014).

select max(DATE_STARTED) from EMPLOYEE;


Why doesn't it return 11/08/14?

Answer

Just to make you understand why are you getting 31/12/99 as the latest date, see the demo .

Your original query:

with sample_table (EMP,DATE_STARTED) as (
                      select 'JOE', '11/08/06' from dual
                     union all
                      select 'BOB', '11/08/14' from dual
                      union all
                      select 'MAY', '12/08/99' from dual

                     )
select max(DATE_STARTED)  
  from  sample_table

Output

12/08/99

I add to_date to and display the dates once again.

with sample_table (EMP,DATE_STARTED) as (
                      select 'JOE',to_date('11/08/06','dd-mm-yy')from dual
                     union all
                      select 'BOB', to_date('11/08/14','dd-mm-yy') from dual
                      union all
                     select 'MAY', to_date('12/08/99','dd-mm-yy') from dual

                     )
select DATE_STARTED-- max(DATE_STARTED)  
  from  sample_table

Output:

11-08-2006
11-08-2014
12-08-2099

Now from the output of the above query you can see that the result which oracle returned is pretty correct. The max date from the above result set is 12-08-99. So the inference from the above is when you don't specify the year as yyyy ,Oracle does implicit conversion and set the yy value depending on its system parameters.

To fix your issue you need something like below.

with sample_table (EMP,DATE_STARTED) as (
                      select 'JOE',to_date('11/08/2006','dd-mm-yy')from dual
                     union all
                      select 'BOB', to_date('11/08/2014','dd-mm-yy') from dual
                      union all
                     select 'MAY', to_date('12/08/1999','dd-mm-yy') from dual

                     )
select max(DATE_STARTED)  
 from  sample_table

Output:

11-08-2014