oracle max function [message #21456] |
Mon, 05 August 2002 05:42 |
Martina
Messages: 3 Registered: August 2002
|
Junior Member |
|
|
Can anybody tell me how can this happen?
select max(meastime) from SYNOP_F;
MAX(MEASTIME)
--------------------
04-MAY-00
1 row selected.
select max(meastime) from SYNOP_F where varID = 7;
MAX(MEASTIME)
--------------------
03-AUG-02
1 row selected.
the data type the variable meastime is DATE
Thank you very much
Martina
|
|
|
Re: oracle max function [message #21457 is a reply to message #21456] |
Mon, 05 August 2002 06:17 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Since we don't see the century, the only way I can think of that this could happen is if the century is different. Try re-running the query and showing the century.
|
|
|
|
|
Re: oracle max function [message #21488 is a reply to message #21456] |
Wed, 07 August 2002 08:15 |
Su
Messages: 154 Registered: April 2002
|
Senior Member |
|
|
Check it out with the revised y2k compliant date format of Oracle. Run your SQL with the date format of RRRR instead of YYYY. For example, check the following one I tried to findout the greatest between two dates without century specification between 01-01-1978 and 01-01-2002.
SQL> ed
Wrote file afiedt.buf
1* select greatest(to_date('01-01-78','mm-dd-yyyy'),to_date('01-01-02','mm-dd-yyyy')) from dual
SQL> /
GREATEST(
---------
01-JAN-78
SQL> ed
Wrote file afiedt.buf
1* select greatest(to_date('01-01-78','mm-dd-rrrr'),to_date('01-01-02','mm-dd-rrrr')) from dual
SQL> /
GREATEST(
---------
01-JAN-02
SQL>
Just rerun your SQL with following amendment.
select max(TO_DATE(TO_CHAR(meastime,'DD-MM-RRRR'),'DD-MM-RRRR')) from SYNOP_F;
Good luck :)
|
|
|