MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) unsigned NOT NULL,
  `birthdate` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`, `birthdate`) VALUES
  ( 1, '1991-01-23'),
  ( 2, 'yyyy-01-23'),
  ( 3, '1991-mm-23'),
  ( 4, '1991-01-dd'),
  ( 5, ''),
  ( 6, '1991-01-d3'),
  ( 7, '1983-05-23'),
  ( 8, '1991-0m-23'),
  ( 9, '19yy-01-23'),
  (10, 'y991-01-23'),
  (11, '1993/03/20'),
  (12, '2000/09/25'),
  (13, '2015.06.30'),
  (14, '2015_04_15');
ALTER TABLE users ADD COLUMN DOB DATE;
SELECT * FROM users;


id
birthdate
DOB


1
1991-01-23
null

2
yyyy-01-23
null

3
1991-mm-23
null

4
1991-01-dd
null

5

null

6
1991-01-d3
null

7
1983-05-23
null

8
1991-0m-23
null

9
19yy-01-23
null

10
y991-01-23
null

11
1993/03/20
null

12
2000/09/25
null

13
2015.06.30
null

14
2015_04_15
null


SELECT 
  SUBSTRING(birthdate, 1, 4) + 
  SUBSTRING(birthdate, 6, 2) AS total_crap
FROM users;

-- the first record, it's actually **ADDING** the values numerically!
-- Gotta love MySQL!!!



total_crap


1992

1

1991

1992

0

1992

1988

1991

20

1

1996

2009

2021

2019


SELECT
  STR_TO_DATE
  (
    CONCAT
    (
      SUBSTRING(birthdate, 1, 4),
      '-',
      SUBSTRING(birthdate, 6, 2),
      '-',
      SUBSTRING(birthdate, 9, 2)
    ), '%Y-%m-%d'
  ) AS dat_str
FROM users;


dat_str


1991-01-23

null

null

null

null

null

1983-05-23

null

null

null

1993-03-20

2000-09-25

2015-06-30

2015-04-15


UPDATE users
SET DOB = 
        STR_TO_DATE
        (
          CONCAT
          (
            SUBSTRING(birthdate, 1, 4),
            '-',
            SUBSTRING(birthdate, 6, 2),
            '-',
            SUBSTRING(birthdate, 9, 2)
          ), '%Y-%m-%d'
        )
WHERE birthdate REGEXP '[0-9]{4}.[0-9]{2}.[0-9]{2}';
SELECT * FROM users;


id
birthdate
DOB


1
1991-01-23
1991-01-23

2
yyyy-01-23
null

3
1991-mm-23
null

4
1991-01-dd
null

5

null

6
1991-01-d3
null

7
1983-05-23
1983-05-23

8
1991-0m-23
null

9
19yy-01-23
null

10
y991-01-23
null

11
1993/03/20
1993-03-20

12
2000/09/25
2000-09-25

13
2015.06.30
2015-06-30

14
2015_04_15
2015-04-15

id	birthdate	DOB
1	1991-01-23	null
2	yyyy-01-23	null
3	1991-mm-23	null
4	1991-01-dd	null
5		null
6	1991-01-d3	null
7	1983-05-23	null
8	1991-0m-23	null
9	19yy-01-23	null
10	y991-01-23	null
11	1993/03/20	null
12	2000/09/25	null
13	2015.06.30	null
14	2015_04_15	null