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    [email protected]  Jack         Smith      1969-03-09   M
2    [email protected] 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    [email protected]  Jack       Smith     1969-03-09 1969   03     09      M
2    [email protected] Jill       Smith     1982-11-29 1982   11     29      F

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

Answer 1

• 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