Re: SQL problem with date types

See my reply in line.

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
These "07-DEC-00" are only the day info, the hour, minute and second info are not display.

>
> SQL> select create_date from USER_ADM_TBL where create_date =
> TO_DATE('07-DEC-2000','DD-MON-YYYY');
>
> no rows selected
>

According to your query, you are trying to find the record which has create_date = midnight of '07-DEC-2000'.

If you want to find all the records which are created on '07-DEC-2000', then

select create_date from USER_ADM_TBL
where create_date >= TO_DATE('07-DEC-2000','DD-MON-YYYY') and create_date < TO_DATE('07-DEC-2000','DD-MON-YYYY') + 1;

> SQL>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Hope this helps.

Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 08 2000 - 22:24:53 CST