Response: Oracle SQL query by date

Comments embedded.

• Original Message ----- From: "vnl" <vnl999_at_vnl999.invalid> Newsgroups: comp.databases.oracle.server,comp.databases.oracle Sent: Thursday, July 29, 2004 6:22 PM Subject: Oracle SQL query by date

> I'm trying to run a SQL query but can't find any records when trying to
> select a certain date. Here's the sql:
>
> SELECT field 1, field2, date_and_time,
> FROM table1
> WHERE date_and_time = '01-SEP-02'
>
> I'm getting no results. The date_and_time field is formatted like this:
>
> 2002-SEP-02 00:01:04
>

It is fairly obvious as to why you get 'no results'. Based upon the data format you WON'T find anything with the query you've written.

> When I run a range, the results show that records do occur on the single
> date that I am looking for:
>
> SELECT field 1, field2, date_and_time,
> FROM table1
> WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'
>
> I'm wondering whether the problem may have something to do with the date
> field containing both the date and time. Any suggestions?
>
> Thanks.

I shall presume date_and_time is a VARCHAR2 column. As such:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time like '2002-SEP-01%';

will return results. If, however, your date_and_time column is a DATE column:

SELECT field 1, field2, date_and_time,
FROM table1
WHERE trunc(date_and_time) = to_date('01-SEP-02', 'DD-MON-YY');

And, if date_and_time IS a DATE column your 'format' is a display format. Such formats also govern how date strings should be entered when not using the to_date() function. Of course, more information on how these columns are defined would help tremendously.

David Fitzjarrell Received on Thu Jul 29 2004 - 19:01:25 CDT