Re: Storing a "fuzzy date"

Absolutely agree Mark's recommendation.

Store dates as DATE, irrespective of whether they are truncated to the hour, the minute, the day, the month or the year. If you want an associated column to show how it's truncated, go ahead. That seems reasonable to me.

The only exception I can think of is if you're an archeaeologist who needs to store dates such as circa 10,000 B.C.

HTH, Paul

"Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0202220635.1d7da334_at_posting.google.com... > "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 --