Re: SQL COMMAND

Dominique PREVOT wrote:

> select * from table where (TO_DATE(CreationDate) < 2002/12/01);

                            ^^^^^^^^^^^^^^^^^^^^^^

That is bad. You should never use a function on column in a WHERE clause, unless there is no other way.

This will result in Oracle not being to use the index on that column. In your example above, it will cause Oracle to result to a full table scan.

If you need to perform a conversion in the WHERE clause, do it on the literals and not the column, i.e.

SELECT
  *
FROM my_table
WHERE creation_date < TO_DATE('2002/12/01','yyyy/mm/dd')

Also keep in mind that if time plays a role, you must be careful as the following SQL will _not_ give you all the rows for the 1st of Dec'02:

SELECT
  *
FROM my_table
WHERE creation_date = TO_DATE('2002/12/01','yyyy/mm/dd')

What it will give you is all the rows created at time 00:00:00 on the 1st of Dec.

If you want all the rows created on that day, you need to do this:

SELECT
  *
FROM my_table
WHERE creation_date BETWEEN
  TO_DATE('2002/12/01 00:00:00','yyyy/mm/dd hh24:mi:ss')   AND
  TO_DATE('2002/12/01 23:59:59','yyyy/mm/dd hh24:mi:ss')

Last comment. Do not use column names like "creationdate". That is not readable. You may write it as "CreationDate" in your code and make it readable in your code.. Oracle does not and cares not how you write it in your code.

Oracle displays the column name as "creationdate". Thus, rather use the underscore to make your column names readable, i.e. "creation_date".

--
Billy