Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Unanswered: Strange behavior

    Hello everyone,

    I'm trying to check for the existence of a record based on a date and an ID.

    If I run the following query, please note that the result is a match:

    SQL> select to_date(jobdate,'dd/MM/yyyy'), to_date('22/03/2002','dd/MM/yyyy') from jobs where pozoid=527;

    TO_DATE(Jjobdate,'dd/MM/yyyy') TO_DATE('22/03/2002','dd/MM/yyyy')
    ------------------------------ ----------------------------------
    22-MAR-02 22-MAR-02

    Now, if I try to count() the number of occurrences to find duplicate records, the following thing happens

    SQL> select count(*) from jobs where pozoid=527 and to_date(JobDate,'dd/MM/yyyy') = to_date('22/03/2002','dd/MM/yyyy');

    COUNT(JOBID)
    ------------
    0

    Any ideas?

    Thanks in advance,

    Miguel Marzano

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    Try truncating the time part !

  3. #3
    Join Date
    Mar 2004
    Posts
    17
    Hi pinakdb,

    I know it's a dummy question, but how do I truncate the time part ?

    Thanks in advance,

    Miguel

    Originally posted by pinakdb
    Try truncating the time part !

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    SQL> select count(*)
    from jobs
    where pozoid=527
    and Trunc(to_date(JobDate,'dd/MM/yyyy')) = to_date ('22/03/2002 00:00:00','dd/MM/yyyy hh24:mi:ss');


    or

    SQL> select count(*)
    from jobs
    where pozoid=527
    and Trunc(to_date(JobDate,'dd/MM/yyyy')) = Trunc(to_date ('22/03/2002','dd/MM/yyyy'));

  5. #5
    Join Date
    Mar 2004
    Posts
    17
    Thanks a lot guys!!!

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    or

    SQL> select count(*)
    from jobs
    where pozoid=527
    and Trunc(to_date(JobDate,'dd/MM/yyyy')) = to_date ('22/03/2002','dd/MM/yyyy');

    as the time defaults to 00:00:00 if you don't specify when creating a date from a literal.

  7. #7
    Join Date
    Mar 2004
    Posts
    17
    This is getting weird...

    Still returns 0 elements.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select count(*)
    from jobs
    where pozoid=527
    and Trunc(JobDate) = to_date ('22/03/2002','dd/MM/yyyy');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Mar 2004
    Posts
    17
    Now, that did the trick.

    Thanks a lot, anacedent!!!



    Originally posted by anacedent
    select count(*)
    from jobs
    where pozoid=527
    and Trunc(JobDate) = to_date ('22/03/2002','dd/MM/yyyy');

Posting Permissions

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