Re: SQL COMMAND

Dominique PREVOT wrote:

> Hi,
> Does anyone know if there is a SQL command that convert a date-time
> value to a date value ?
>
> For instance,
>
> select * from table where (TO_DATE(CreationDate) < 2002/12/01);
>
>
> (where CreationDate is a date-time field (yyyy/mm/dd:hh:mm:ss:lll))
>
> Thanks for help,
> Dominique
>

Hi Dominique,
If you want a quick answer search the archives for the newsgroup. By going to google, clicking on groups, and searching in comp.database.oracle I found this from 1999:

Oracle does something slightly strage with date comparisons, it includes the time component in the calculation when not forced not to do this. Thus, by Oracle logic a record dated 01/01/1999 00:00:01 meets the > '01-NOV-1999' criterion. You need to either code either: SELECT BLAH_DT
FROM BLAH
WHERE BLAH_DT >= TO_DATE('02-NOV-1999'); or
SELECT BLAH_DT
FROM BLAH
WHERE TRUNC(BLAH_DT) > TRUNC(TO_DATE('01-NOV-1999'); unless you can be absolutely certain that all records have 00:00:00 times components. TRUNC() eliminates the time component from the comparison.

Try:
 SELECT TO_char(trunc(sysdate), 'Month dd, YYYY, HH:MI A.M.') FROM DUAL;

hth,
Jen Received on Tue Dec 17 2002 - 11:48:37 CST