converting date in abbreviated string format to date format in mysql

Question

I have a table consisting of 2 columns : id and month_year

id  month_year 

1   Dec 2016
2   Jan 2017
3   Feb 2017
4   Mar 2017
.
...
.......

I need a result consisting of the date format like the following

        id  month_year 

        1   2016-12-01
        2   2017-01-01
        3   2017-02-01
        4   2017-03-01
        ..
        ....
        ......

      (We can assume the date to be 1st)

I could achieve it with hardcoding it

 SELECT id , case when a.month_year = 'Dec 2016' THEN 
 STR_TO_DATE('01/12/2016','%d/%m/%Y')
 when a.month_year = 'Jan 2017' THEN 
 STR_TO_DATE('01/1/2017','%d/%m/%Y')
 when a.month_year = 'Feb 2017' THEN  
STR_TO_DATE('01/2/2017','%d/%m/%Y')
 when a.month_year = 'Mar 2017' THEN  
STR_TO_DATE('01/3/2017','%d/%m/%Y')
 end as month_year from [table] a

BUT I want it WITHOUT HARCODING it , when the new data comes this query wont work..how can i proceed further ?

Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — Sebastian Brosch, Dec 15 '17 at 10:06

score 0 · Accepted Answer · answered Dec 15 '17 at 10:03

E.g.:

SELECT STR_TO_DATE(CONCAT('Dec 2016','01'),'%b %Y %d');
+-------------------------------------------------+
| STR_TO_DATE(CONCAT('Dec 2016','01'),'%b %Y %d') |
+-------------------------------------------------+
| 2016-12-01                                      |
+-------------------------------------------------+

score 0 · Answer 2 · answered Dec 15 '17 at 10:08

0

SELECT DATE_FORMAT(STR_TO_DATE('Dec 2017','%M %Y'), '01/%m/%Y');

Output: 01/12/2017

answered Dec 15 '17 at 10:08

vadzim dvorak

939
6
24

converting date in abbreviated string format to date format in mysql

2 Answers2