How can I separate a single column into 3 separate columns-CodePudding

Want to execute a query to view single date-month-year time column to separate date column, month column and year column.

 joining_date
 01-JAN-22 12.00.00AM

joining_date|joining_month|joining_year
          01       |     JAN      |    22

CodePudding user response：

Use YEAR, MONTH and DAY syntax:

SELECT  
YEAR(`joining_date`) as joiningYear, 
MONTH(`joining_date`) as joiningMonth, 
DAY(`joining_date`) as joiningDay
FROM tableName

If you want your month name, then use MONTHNAME:

SELECT  
YEAR(`joining_date`) as joiningYear, 
MONTHNAME(`joining_date`) as joiningMonth, 
DAY(`joining_date`) as joiningDay
FROM tableName

CodePudding user response：

You have some ways of doing this:

If your data is always in this 01-JAN-22 12.00.00AM format , no matter what comes after 22, you can use substring.

select substring('01-JAN-22 12.00.00AM',1,2) as joining_date,
       substring('01-JAN-22 12.00.00AM',4,3) as joining_month,
       substring('01-JAN-22 12.00.00AM',8,2) as joining_year;

Result:

joining_date  joining_month   joining_year
      01           JAN              22

Another option is converting the string to proper date datatype an use MySQL functions, like :

select DAY(str_to_date('01-JAN-22 12.00.00AM', "%d-%b-%y")) as joining_date,
       MONTH(str_to_date('01-JAN-22 12.00.00AM', "%d-%b-%y")) as joining_month,
       YEAR(str_to_date('01-JAN-22 12.00.00AM', "%d-%b-%y")) as joining_year ;

Result:

joining_date  joining_month   joining_year
      1            1             2022

Fiddle