How do I parse a MySQL date string where the month doesn't have a leading zero?

Question

How do I convert strings like '1021970' (January 2, 1970) or '12021970' (December 2, 1970) with varying month lengths into MySQL dates?

Answer 1

In a string where the month doesn't have a leading zero (i.e. sometimes it has one digit, sometimes it has two digits), the day always has a leading zero, and the year is always four digits:

SELECT
    IF(
        LENGTH(`your_date_column`) = 7,
        STR_TO_DATE(INSERT(`your_date_column`, 1, 0, '0'), '%m%d%Y'),
        STR_TO_DATE(`your_date_column`, '%m%d%Y')
    );

To test:

-- January 2, 1970.
SELECT
    IF(
        LENGTH('1021970') = 7,
        STR_TO_DATE(INSERT('1021970', 1, 0, '0'), '%m%d%Y'),
        STR_TO_DATE('1021970', '%m%d%Y')
    );

-- December 2, 1970.
SELECT
    IF(
        LENGTH('12021970') = 7,
        STR_TO_DATE(INSERT('12021970', 1, 0, '0'), '%m%d%Y'),
        STR_TO_DATE('12021970', '%m%d%Y')
    );