Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL problem with date types
Hi Bob,
I think your problem lies in the fact that date columns also contain a time element..in the first select you allow for 'greater than or equal to' so anything later (greater than) DEC 06 at midnight gets returned..in the second select you're specifying rows that must exactly equal both the date and time (to the nano second) the equal sign is the problem..or, truncate the date column so it just returns dates with no times.
Hope this helps,
Steve
In article <90r4p7$pdo$1_at_nnrp1.deja.com>,
leonardra_at_my-deja.com wrote:
> If this is wrong forum, please let me know.
>
> I'm having a problem doing selects with date type.
> I have two selects below and don't know why the second
> one doesn't work. If you know how to get it to work,
> let me know.
>
> Thanks,
> Bob Leonard
>
> SQL> select create_date from USER_ADM_TBL where create_date >
> TO_DATE('06-DEC-2000','DD-MON-YYYY');
>
> CREATE_DA
> ---------
> 06-DEC-00
> 07-DEC-00
> 07-DEC-00
> 07-DEC-00
> 07-DEC-00
>
> SQL> select create_date from USER_ADM_TBL where create_date =
> TO_DATE('07-DEC-2000','DD-MON-YYYY');
>
> no rows selected
>
> SQL>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Dec 08 2000 - 11:25:43 CST