manhattes Posted February 4, 2016 Share Posted February 4, 2016 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? Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted February 4, 2016 Solution Share Posted February 4, 2016 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 | +----+----------+------------+ 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.