database date fields

From: Hari Thanigaraj <hari_at_cardsfellow.wustl.edu>
Date: Thu, 23 Jul 1998 23:06:49 -0500
Message-ID: <35B80859.EDA@cardsfellow.wustl.edu>

Hi!

I am having problems querying date fields in the database. I am trying to write a query in these lines.

select * from my_table where
date_field >= (select nvl(:from_date,min(date_field)) from

                my_table)
 and date_field <= (select nvl(:to_date,max(date_field)) from
                    my_table)

(subqueries used to handle optional input parameters)

date_field - database field (datetime)
date_parameter - input field(date)

Suppose the database Record has value 05-28-1998 01:01:01 and the input field has value 05-28-1998 there still is no match because of the time stamp.

The solution might convert the dates using to_char with 'YYYYMMDD" mask for
both the database field and the input date field. However the statement gets more complex. And also it would'n be very elegant. Is there any other solution?. A funtion to extract the datepart alone from the database field perhaps ?

Any suggestion would be greatly appreciated. Received on Thu Jul 23 1998 - 23:06:49 CDT