On 9/14/05, Joe <svn@freedomcircle.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.