Select from table by knowing only date without time (ORACLE)

Question

I'm trying to retrieve records from table by knowing the date in column contains date and time. Suppose I have table called `t1` which contains only two column `name` and `date` respectively. The data stored in column date like this `8/3/2010 12:34:20 PM`. I want to retrieve this record by this query for example (note I don't put the time): ``` Select * From t1 Where date="8/3/2010" ``` This query give me nothing ! How can I retrieve `date` by knowing only `date` without the time?

Accepted Answer

`DATE` is a reserved keyword in Oracle, so I'm using column-name `your_date` instead.

If you have an index on `your_date`, I would use

```
WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')
```

or `BETWEEN`:

```
WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
```

If there is no index or if there are not too many records

```
WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')
```

should be sufficient. `TRUNC` without parameter removes hours, minutes and seconds from a `DATE`.

If performance really matters, consider putting a `Function Based Index` on that column:

```
CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));
```

Select from table by knowing only date without time (ORACLE)

Solution

Problem