Split date into multiple columns

Question

I've been searching for hours but haven't been able to find an answer. I'm hoping somebody might know how to split a date into 3 columns. I have a mysql table that looks like the following

Id   Email     First_Name   Last_Name  DOB          Gender
1    yo@1.com  Jack         Smith      1969-03-09   M
2    yo1@1.com Jill         Smith      1982-11-29   F

I want the final format to be as follows

Id   Email     First_Name Last_Name DOB        DOB_Yr DOB_Mo DOB_Day Gender
1    yo@1.com  Jack       Smith     1969-03-09 1969   03     09      M
2    yo1@1.com Jill       Smith     1982-11-29 1982   11     29      F

I have access to HeidiSql and MySQL Workbench. Does anybody how to do this? Thanks.

Do you want with leading zeros also, for eg: `03` instead of `3` for month ? — Madhur Bhaiya, Oct 04 '18 at 17:35
Dare we ask why? Anyway, consider handling issues of data display in application code — Strawberry, Oct 04 '18 at 18:19

Madhur Bhaiya · Accepted Answer · 2018-10-04T18:48:41.900

You can use various Date functions, like YEAR(), MONTH(), and DAY(), to get the year, month and day value from a MySQL date.

Try:

SELECT Id, 
       Email, 
       First_Name, 
       Last_Name, 
       DOB, 
       YEAR(DOB) AS DOB_Yr, 
       MONTH(DOB) AS DOB_Mo, 
       DAY(DOB) AS DOB_Day, 
       Gender 
FROM your_table

Incase you want leading 0s in Month and Day (03 instead of 3), you can use the Lpad() function. Try the following instead:

SELECT Id, 
       Email, 
       First_Name, 
       Last_Name, 
       DOB, 
       YEAR(DOB) AS DOB_Yr, 
       LPAD(MONTH(DOB), 2, '0') AS DOB_Mo, 
       LPAD(DAY(DOB), 2, '0') AS DOB_Day, 
       Gender 
FROM your_table

Split date into multiple columns

1 Answers1