Converting date format

[manhattes]

I imported a table into mysql and the dates within the table are formatted "16-Feb" where the 16 is the Year not the day. Day has no value. I tried to convert it in excel first but it kept switching the day with the year.

 

Which command should i use to format the dates properly within SQL?

 

[Barand]

You can use sql's STR_TO_DATE() function. Concatenate "-01" to the end first so you don't get day 0.

 

Example

CREATE TABLE `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str_date` varchar(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
)

mysql> SELECT * FROM test_date;
+----+----------+------+
| id | str_date | date |
+----+----------+------+
|  1 | 16-Jan   | NULL |
|  2 | 16-Feb   | NULL |
|  3 | 16-Mar   | NULL |
+----+----------+------+

UPDATE test_date
SET date = STR_TO_DATE(CONCAT(str_date, '-01'), '%y-%b-%d'); 

mysql> SELECT * FROM test_date;
+----+----------+------------+
| id | str_date | date       |
+----+----------+------------+
|  1 | 16-Jan   | 2016-01-01 |
|  2 | 16-Feb   | 2016-02-01 |
|  3 | 16-Mar   | 2016-03-01 |
+----+----------+------------+