Unable to query Date type field correctly

[JasonDBurke]

Hello,
I am querying a field of type DATE within the Oracle database. I would like to return the Maximum (most current) Date within the database. The Date String in the DB is stored in this format:
dd-mmm-yyyy hh:mm:ss

I've been trying a string like:

this doesn't work
SELECT MAX(DATEFIELD) FROM MAINTABLE.EVENT

this doesn't work
SELECT GREATEST(DATEFIELD) FROM MAINTABLE.EVENT

this doesn't work either
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD > "11-Jan-1960 01:01:01 AM"

or
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD > 0


Any suggestions are welcome!!! Thanx

 

[sem]

What do you mean by
The Date String in the DB is stored in this format:
dd-mmm-yyyy hh:mm:ss

If you use varchar2 field to store dates, try to select
max(to_date(your_field, 'dd-mmm-yyyy hh:mm:ss'))

But for many reasons it's much more convinient to store dates in DATE fields.