MYSQL: How to convert VARCHAR column containing dates to a DATE column?

I have a large table of birthdays that I want to convert from a varchar column to a date column.

The table SQL is:

CREATE TABLE `birthdays` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) DEFAULT NULL,
  `birthday_date` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

The birthday date is stored in one of two ways: strings like "05/26/1994" or sometimes "03/14" (for people who don't want to reveal their birth year).

What is the best way to create another table and store the birthdays in a date column? Is it possible to do this just using MySQL (and avoid using P开发者_StackOverflow社区HP or some other intermediary)?

I have found a STR_TO_DATE function in MySQL. Should I use this?

Thanks!

---

SELECT IF(birthday_date RLIKE '[0-9]{2}/[0-9]{2}/[0-9]{4}',STR_TO_DATE(birthday_date,'%m/%d/%Y'),STR_TO_DATE(birthday_date,'%m/%d'));

This will result in dates like 0000-03-14 for rows that have no year entered. Your server needs to be configured to allow invalid dates though (see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html )

---

If you convert your column to DateTime then you will not be able to store dates like "03/14" in which year is missing. So instead I suggest to keep this as it is and probably have another column for storing the dateTime if you really need that. Also have internal trigger to convert the datestrings from varchar column to dateTime column.

yes, you have to use the method STR_TO_DATE for your purpose.