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
