Re: Storing a "fuzzy date"

Michael G. Schneider wrote:

> With Oracle 8.1.7
>
> In my application I would like to support a "fuzzy date". The database
> should not only hold specific dates (as e.g. Feb 20th 2002), but also
> month/year-based dates as "Feb 2002" or just "2002".
>
> Any suggestions on how this might be done, without loosing the
> DATE-functionality too much.
>
> Michael G. Schneider
>
>
>

Kind of fuzzy question, without having redundant data, a table can't hold both Feb 20th 2002, Feb 2002 and 2002. But if you only need this a kind for query, try with function based index on the date column.
Create a package to hold the date functions Use the package to create the needed indexes. create index table_fbi_function on table (package_name.function_name(date column)) tablespace ....

In your query use
where package_name.function_name(date_column) = package_name.function_name(to_date(:bind_fixed_date,'format'))

Having query_rewrite=true and query_rewrite_integrity=trusted, will persuade the optimizer to use the index if it 'pays'.

Untill patch 8.1.7.3.0 an index with buildin function as create index on table (trunc(date_column,['MM' | 'YYYY'] would give Ora-600 at least on Wintell platform (I think it is generic) Haven't tried with 81730 yet.

/Svend Jensen Received on Thu Feb 21 2002 - 12:14:17 CST