Re: Oracle SQL query by date

From: vnl <vnl999_at_vnl999.invalid>
Date: Fri, 30 Jul 2004 14:47:35 -0500
Message-ID: <Xns9536A0A6881EEvnl999@216.196.97.131>

afilonov_at_yahoo.com (Alex Filonov) wrote in news:336da121.0407300808.690ded59_at_posting.google.com:

> vnl <vnl999_at_vnl999.invalid> wrote in message
> news:<Xns9535C4FA974ABvnl999_at_216.196.97.131>... 

>> 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
>>

> 
> And here lies the problem. You date is not equal to '01-SEP-02', it
> has time component as well. You can either include time in your query
> condition:
> 
> WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD
> HH24:MI:SS') 
> 
> or use trunc function to truncate date before comparing it to the
> constant: 
> 
> WHERE trunc(date_and_time) = '01-SEP-02'
> 
> 

>> 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.

Thanks everyone. The field did turn out to be a "date" type field.

I was eventually able to get it to work by using the following format:

SELECT field1, field2, date_and_time,
FROM table1
WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')

What was weird was that I was getting different results in the date_and_time field depending on whether I was running the SQL in Toad, Oracle's SQL program (forgot name), and Crystal Reports SQL Designer. Two showed both the date and time, the other showed just the date while running the same SQL query. It got even worse as I tried to import the data into Excel and Access which added further formatting decisions.

I'm still working on getting the SQL query to remove the time entirely so that I will just have the date in that field.

Thanks.   Received on Fri Jul 30 2004 - 14:47:35 CDT