Re: comparing a date?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 21 Jun 2006 05:48:08 -0700
Message-ID: <1150894088.158794.102830@c74g2000cwc.googlegroups.com>

Brian Peasland wrote:
> Mark Harrison wrote:
> > How do I compare a date to be equal to a particular day?
> >
> > For example, here's some dates in the all_users table:
> >
> > select * from all_users;
> >
> > USERNAME USER_ID CREATED
> > ------------------------------ ---------- ---------
> > MAB 235 20-JUN-06
> > MPLANCK 234 20-JUN-06
> > TBEST 233 20-JUN-06
> >
> > but if I just check for date equality, I don't match
> > any rows:
> >
> > SQL> select * from all_users where created='20-JUN-06';
> >
> > no rows selected
> >
> > This query does the right thing:
> >
> > select * from all_users where created>'20-JUN-06' and created<'21-JUN-06';
> >
> > What's the proper way to compare a date column for a particular
> > day, without respect to the time?
> >
> > Thanks!
> > Mark
> >
>
> You'll have to convert the date to a character string and then compare
> the string representation of the day in question. For example:
>
> SELECT * FROM ALL_USERS WHERE TO_CHAR(CREATED,'MM/DD/YY')='12/31/05';
>
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> oracle_dba_at_nospam.peasland.net
> http://www.peasland.net
>
>

Mark, if the DATE column is indexed queries that convert the date to character will disable the use of the index while queries written like "select * from all_users where created>'20-JUN-06' and created<'21-JUN-06'; " can potentially use the index.

You can also compare a trunc of the sysdate which sets the time to midnight to the to_date of the date:

ddc1 > select app_code, substr(message,1,30) as message   2 from eds_error
  3 where trunc(run_date) = trunc(sysdate)   4 and rownum < 11
  5 /

APP_CODE MESSAGE

---------- ------------------------------

R51OETSDLG Logging turned on

SHIP_TRACK MSG-012 in LOOP - After update
SHIP_TRACK MSG-013 in LOOP - p_shipper_no
SHIP_TRACK MSG-014 in LOOP - Before do_ca
SHIP_TRACK MSG-015 in LOOP - After do_cal
SHIP_TRACK MSG-016 in LOOP - before oep$i
SHIP_TRACK MSG-017 in LOOP - before jitwh
SHIP_TRACK MSG-018 in LOOP - before jitwh
SHIP_TRACK MSG-019 in LOOP - after jitwhs
SHIP_TRACK MSG-002 in LOOP: p_shipper_no

10 rows selected.

HTH -- Mark D Powell -- Received on Wed Jun 21 2006 - 07:48:08 CDT