Re: Storing a "fuzzy date"

Er, had *just* that problem with my mother-in-law's date of birth.

Not just archaeologists, therefore.

What's that bit in the US Constitution about 'cruel and unusual punishment'? Eh?!

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3c76ae73_1_at_mk-nntp-1.news.uk.worldonline.com...


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

>

>