Re: Storing a "fuzzy date"

"Michael G. Schneider" <mgs_at_mgs-software.de> wrote in message news:<a554v7$oed$00$1_at_news.t-online.com>...
> "Michael G. Schneider" <mgs_at_mgs-software.de> schrieb im Newsbeitrag
> news:a533dk$t6$02$1_at_news.t-online.com...
> > 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".
>
> Sorry, if my question hasn't been clear enough. I was mostly thinking about
> how to store this data in a the database. Two ideas come to my mind:
>
> [1] A normal DATE column plus an CHAR(1) column describing, whether the DATE
> column describes a dd.mm.yyyy or a mm.yyyy or a yyyy.
>
> [2] A CHAR(10) column holding data as "2002-02-21" or "2002-02" or "2002",
> where the column is self explaining and does not need an additional column.
>
> So question is: has anybody developed an application with this kind of
> "fuzzy date"? Did you choose [1] or [2] or maybe something different?
>
> Michael G. Schneider

Mike, I can give you some suggestions from the experience of having to work trying to match up two tables where one of the tables had the date stored as varchar2 and the dates were in the format of mm/dd/yy, mm-dd-yy, dd-mon-yy, mon yy, ddFEyy, and a few others. I wrote a pl/sql routine to normalize the dates into a yyyymmdd format that I could use to compare accross.

The best advice I can offer is to store the dates using the Oracle date datatype and associate a format mask indicator with the date field. You would need to normalize the month year entires to always be the first day of the month or some standard.

Using an Oracle date would allow reformating via to_char in any format Oracle supports, while the associated date input mask indicator would allow you to format the date as it was input where desired via a database stored function. You could code the function into a view and make the formatting automatic on retrieval.

But you really do not want the dates stored as character datatypes. It will bite someone in the hind-quarters sooner or later. IMHO.

• Mark D Powell --