Re: Partial dates

On 9/14/05, Joe <svn(at)freedomcircle(dot)net> wrote:
>
> Joshua D. Drake wrote:
> > Well the easy solution is to just make the date a text type but that is
> > the wrong answer. The right answer is to fix the data set.
> > MySQL should never have allowed you do insert those dates in the first
> > place. I know that doesn't help you much but at some point
> > you are going to have to sanitize the data anyway.
>
> I'm not sure I agree with the need to "fix" or "sanitize" the data. The
> columns
> in question are used mostly for publication dates. While you may be able
> to
> find a full release date for recent books, they are generally listed as
> published on a given year, period. Most monthly magazines only have a
> month-year of publication. And of course, daily newspapers and Internet
> articles usually have a full day-month-year. In fact, the MySQL solution
> didn't
> address quarterly or bi-monthly publications as that data was only
> captured as
> one of the months in the period--as opposed to Mar/Apr 2005 or First
> Quarter
> 2005 (or worse: Winter 2004). As Tom Lane argued, it seems I'll have to
> bite the
> bullet and create a new datatype. The only other alternative I see would
> be to
> split the publication date into three columns and that's rather ugly.
>
> Thanks for the feedback.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

probably you can add a new column to identify the publication type and
sanitize the wrong dates ( like 2005-00-00 to 2005-01-01). Now based on the
publication type you can extract the portion of the date you want.

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.