Search code examples
mysqldatestr-to-date

Update DATE in tables stored as VARCHAR after conversion

I imported data from Excel and brought dates over in VARCHAR format as 03/24/2017 because the dates were not saving. So I used VARCHAR for expediency. Now I am paying the price.

I am trying to use the STR_TO_DATE function to convert so that I can UPDATE a new column (defined as DATE) in the row.

When I use the following it looks promising, but only the month/day convert properly, the year becomes 2020 as shown below:

date_order $conv_date_order

3/24/2017 2020-03-24

    SELECT  date_order, STR_TO_DATE(date_order, '%m/%d/%y')     AS $conv_date_order,
            date_shipped, STR_TO_DATE(date_shipped, '%m/%d/%y') AS $conv_date_shipped,
            date_need, STR_TO_DATE(date_need, '%m/%d/%y')       AS $conv_date_need
    FROM    Orders
    WHERE   Orders.id = $id;
Solution

  • You need to use a capital Y in your '%m/%d/%Y' format string to indicate a four digit year. The lowercase y indicates a two digit year.