Re: comparing a date?

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

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown