I'm trying to SEARCH for DISTINCT records in the database based upon a date field. For example, I want to group all records for July 26, 2001. Unfortunately the fields contain not only the date, but the time also. So when I run my query...
SELECT DISTINCT settle_dte FROM heloc_tracking WHERE settle_dte > TO_DATE('25.7.2001','DD.MM.YYYY') AND settle_dte < TO_DATE('27.7.2001','DD.MM.YYYY') ORDER BY settle_dte
...I get a result set of four different records with the same date but four different times.
How can I get the search to only look at the date part of the fields so when I SEARCH for DISTINCT records, I'll only get the one record.
7/26/2001.
Any ideas please?
07-26-2001, 02:17 PM
chrisrlong
SELECT DISTINCT trunc(settle_dte) FROM heloc_tracking WHERE settle_dte > TO_DATE('25.7.2001','DD.MM.YYYY') AND settle_dte < TO_DATE('27.7.2001','DD.MM.YYYY') ORDER BY settle_dte
- Chris
07-26-2001, 02:53 PM
storinger
I tried to use the trunc() idea, but I got the following error message...
Microsoft OLE DB Provider for Oracle error '80004005'
ORA-01791: not a SELECTed expression
Am I missing something else?
07-26-2001, 03:02 PM
marist89
Sounds like an ODBC incompatibility...
07-26-2001, 03:03 PM
marist89
If you are running this query multiple times, you can create a view with the TRUNC() function built-in and select from the view...
07-26-2001, 03:22 PM
chrisrlong
SELECT DISTINCT trunc(settle_dte) FROM heloc_tracking WHERE settle_dte > TO_DATE('25.7.2001','DD.MM.YYYY') AND settle_dte < TO_DATE('27.7.2001','DD.MM.YYYY') ORDER BY trunc(settle_dte)
Or it could be yelling about the ORDER BY column not matching the SELECT? Wrap both in a trunc() and try again.
- Chris
07-26-2001, 04:24 PM
storinger
That worked.
I had to put the trunc() in the output too to get it to work, but it gave me the correct output.
