Search code examples
sqlmysqldatestr-to-date

MySQL: Date String to Date to Month coming back as Null

The dates in this database are in the form of a string, and in this format:

"1/5/2019"

I can use this query to convert the string dates to a date:

SELECT  STR_TO_DATE(`date`, '%m/%d/%Y') FROM `supermarket_sales`

Comes back in this format:

2019-01-05

Not really sure why, especially since it's %m/%d/%Y, but that's not my main problem.

What I need to do now is just get the month spelled out. So obviously, the above should come back like this:

January

So my attempt to make this work, I am using this query:

select DATE_FORMAT(STR_TO_DATE(`date`,'%d %M %Y') ,'%d-%m-%Y') FROM `supermarket_sales`

But I am getting NULL values in return.

How can I make this work?

Solution

  • you can use MONTHNAME

    SELECT  MONTHNAME(STR_TO_DATE('1/5/2019', '%m/%d/%Y'))
    MONTHNAME(STR_TO_DATE('1/5/2019', '%m/%d/%Y'))
    January

    fiddle

    But te best would be to save the data in a proper mysql date format 2019-01-05 converting cost time and precious resources