Oracle max function returns min

Question

I am getting the wrong result from the oracle max function. It seems to be selecting the min date rather than the max date. I have simplified the table.

Oracle Version: "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0" Table:

  ID_PLCMT_EVENT    NUMBER(16,0)
  DT_PLCMT_END      DATE
  ID_CASE           NUMBER(16,0)

Query:

select id_plcmt_event, dt_plcmt_end,id_case from placement where id_case=47626279;

Result:

350048317   09-MAR-21   47626279
350876791   20-MAY-21   47626279
355789596   21-NOV-22   47626279
393605551   17-FEB-23   47626279
389981360   31-JAN-23   47626279
394670566   31-DEC-12   47626279

Query:

select max(dt_plcmt_end) from placement where id_case=47626279;

Result:

31-DEC-12

Year 2031, December, 12:th? Add `ORDER BY dt_plcmt_end` to query 1 and show us the result. — jarlh, Mar 29 '23 at 19:51
What date format is your database using? Run `select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';` to check. I bet you're using YY-MON-DD. — Sam M, Mar 29 '23 at 19:56
See https://stackoverflow.com/questions/50163432/oracles-default-date-format — Lesiak, Mar 29 '23 at 19:58
If you `select to_char( dt_plcmt_end, 'yyyy-mm-dd' )`, I will wager that you'll see that the year on some or all of the dates are not what you expect. Most likely, some dates that you think are 2022 or 2023 are actually 1922 or 0023. — Justin Cave, Mar 29 '23 at 20:18

MT0 · Answer 1 · 2023-03-29T20:25:01.243

You have 2-digit years so one date might be 1921 and the other 2013 and it is not obvious if your date format is RR-MON-DD, which is the default date format for Hungary, or DD-MON-RR, which is the default date format from USA, China, UK and several others (see Oracle's Default Date format).

To see the entire date, you can use:

select id_plcmt_event,
       TO_CHAR(dt_plcmt_end, 'YYYY-MM-DD HH24:MI:SS') AS dt_plcmt_end,
       id_case
from   placement
where  id_case=47626279;

and:

select TO_CHAR(max(dt_plcmt_end), 'YYYY-MM-DD HH24:MI:SS') AS max_dt_plcmt_end
from   placement
where  id_case=47626279;

If you are using SQL*Plus or SQL Developer then you can change the default date format using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

For other client applications there will typically be a preference you can change in the settings for the date format.

Oracle max function returns min

1 Answers1