CODEWITHSUNDEEP
mysql
Greg Viv
Greg Viv

Reputation: 847

Update date in MySQL to contain leading zeros

Let's say I have a column col1 in table tab1 that has following mixed format dates:

2015-03-03
2015-02-03
2017-3-6
2015-03-04
2017-11-6

What would be a good MySQL query to update this column to something more consistent, so all dates contain leading zeros

Upvotes: 2

Views: 989

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

As a disclaimer, to ward off evil spirits as well at that downvoter who has hit me like 6 times today, you should be storing your dates as...dates, and not as text. That being said, if you have a lot of text data in this format, you can fairly easily convert it to a date by using MySQL's string functions. The month and day fields, which may be either one or two digits, will need to be left-padded with a zero. Then, use STR_TO_DATE with the appropriate format mask to bring the standardized date text to a bona-fide date.

Try the following query:

SELECT
    STR_TO_DATE(CONCAT(SUBSTRING_INDEX(col1, '-', 1),
                    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(col1, '-', 2), '-', -1), 2, '0'),
                    LPAD(SUBSTRING_INDEX(col1, '-', -1), 2, '0')), '%Y%m%d') AS new_date
FROM yourTable;

If you want to update a new column in your table, you can reuse the above logic almost verbatim, just do an UPDATE instead of a SELECT.

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

ahmad
ahmad

Reputation: 2729

You should be storing these as a date field to begin with but anyway here's your query

UPDATE `tab1` SET `col1` = DATE(`col1`);

Upvotes: 4

Related Questions