Re: Response: Oracle SQL query by date

From: vnl <vnl999_at_vnl999.invalid>
Date: Thu, 29 Jul 2004 19:44:58 -0500
Message-ID: <Xns9535D310091D6vnl999@216.196.97.131>

fitzjarrell_at_cox.net (David Fitzjarrell) wrote in news:9711ade0.0407291601.7dae7bcb_at_posting.google.com:

> 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

Thanks David. I'm a beginner at SQL so I appreciate your help. Received on Thu Jul 29 2004 - 19:44:58 CDT