Issue while converting string to date in MySQL

Question

I have following table with records:

create table tblte
(
    dat varchar(50)
);

insert into tblte(dat) values('Sep 07 2018');
insert into tblte(dat) values('Sep 04 2018');
insert into tblte(dat) values('Sep 10 2018');

I tested following query

SELECT STR_TO_DATE(`dat`, '%MM %D %Y') FROM tblte

I got below results. They all show null. Any idea why this is happening?

Answer 1

It would be best to store the as dates, because converting costs time and you have it to do all the time

create table tblte
(
    dat varchar(50)
);

insert into tblte(dat) values('Sep 07 2018');
insert into tblte(dat) values('Sep 04 2018');
insert into tblte(dat) values('Sep 10 2018');

SELECT STR_TO_DATE(`dat`, '%b %d %Y') FROM tblte

| STR_TO_DATE(`dat`, '%b %d %Y') |
| :----------------------------- |
| 2018-09-07                     |
| 2018-09-04                     |
| 2018-09-10                     |

db<>fiddle here