mysqlsqldateselectstr-to-date

str_to_date change year data MySQL

I am trying to change the format of my date which is mm/dd/yyyy to MySQL format yyyy-mm-dd.

But it changes the data year to 2020.

The product_history table:

log_date    id      stock
10/30/2018  1001    59    
10/30/2018  1002    35   
10/30/2018  1003    54    
10/30/2018  1004    40   
10/30/2018  1005    5     
10/30/2018  1006    20   
10/30/2018  1007    69   
...

This is my code:

SELECT *, str_to_date(my_schema.product_history.log_date, '%m/%d/%y') as new_log_date FROM my_schema.product_history

Output:

log_date    id      stock new_log_date
10/30/2018  1001    59    2020-10-30
10/30/2018  1002    35    2020-10-30
10/30/2018  1003    54    2020-10-30
10/30/2018  1004    40    2020-10-30
10/30/2018  1005    5     2020-10-30
10/30/2018  1006    20    2020-10-30
10/30/2018  1007    69    2020-10-30
...
Solution

  • %y represents a year in two digits. Since you have four digits, you should use %Y instead:

    SELECT *, str_to_date(my_schema.product_history.log_date, '%m/%d/%Y') AS new_log_date 
-- Here ----------------------------------------------------------^
FROM   my_schema.product_history