Distinct Date Search on Date with Time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Distinct Date Search on Date with Time

  1. #1
    Join Date
    Jul 2001
    Location
    Naperville, IL
    Posts
    5
    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.

    7/26/2001 06:21:54
    7/26/2001 08:43:21
    7/26/2001 09:25:18
    7/26/2001 12:23:08

    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?

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Jul 2001
    Location
    Naperville, IL
    Posts
    5
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sounds like an ODBC incompatibility...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you are running this query multiple times, you can create a view with the TRUNC() function built-in and select from the view...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  7. #7
    Join Date
    Jul 2001
    Location
    Naperville, IL
    Posts
    5

    Cool

    That worked.

    I had to put the trunc() in the output too to get it to work, but it gave me the correct output.

    Thanks


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width