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

 

[carp]

When you say ". I would like to return the Maximum (most current) Date within the database.", what exactly do you mean?
If you mean to say you would like to return the most recent date in a specific column in a specific table, then

SELECT max(my_date_field) FROM my_table;

should do the job for you. If you have tried this and did not get the results you wanted, then we need to go deeper (i.e., what IS the most recent date vs what was returned).

 

[JasonDBurke]

I am querying the db via MVC++. The query returns nothing and the SQLFetch function returns unsuccessful as the query value. I can query other columns specifically and have no problems, i can also query the entire table and pull the date_field info through column binding. But a specific query on the date_field column for some reason is unsuccessful. ??? I'm not sure why. If what i said makes any sense at all

 

[carp]

Are you able to get the SQL to work using SQL*Plus? If so, then the problem may be in the MVC++, not the database. It could be a bug; if so, it's probably pretty well known so the MVC++ folks can probably help you with this.

 

[oliveng]

In Oracle SQL (e.g. SQL plus, Oracle Form, Oracle report),
1. In Oracle default, it will date field in format
'DD-MON-YY' e.g. 01-JAN-99.

You can change this statement (to get a max date)
SELECT MAX(DATEFIELD) FROM MAINTABLE.EVENT
to
SELECT TO_CHAR(MAX(DATEFIELD),'DD-MON-YYYY HH24:MI:SS') FROM MAINTABLE.EVENT
then the result will display in a date format string.

2a. As you want to compare a date field with time, you need to to_date function to convert a date time string.
i.e. change this statement
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD > "11-Jan-1960 01:01:01 AM"
to
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD > TO_DATE('11-JAN-1960 01:01:01AM','DD-MON-YYYY HH24:MI:SS')

3. A string uses ' rather than ".
e.g. 'abc' is correct, "abc" is incorrect.

4.If you want to check the date field is inputted or not, you can use NULL, so you should change this statement
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD > 0
to
SELECT * FROM MAINTABLE.EVENT WHERE DATEFIELD IS NOT NULL