Extracting Monthname and Year from a string

Question

I have to extract the month name and year from the string of the form mmm-yy (Eg. Jan-20) using mysql query. I have tried the following query it returns only NULL. The query is:

SELECT monthname(mon)FROM month_extract_plan

Sample data: 

Jan-20
Feb-20
Dec-19

any chance that you can add sample data? – Professor Abronsius Feb 24 '20 at 12:03 — Professor Abronsius, Feb 24 '20 at 12:03

score 1 · Answer 1 · answered Feb 24 '20 at 12:08

1

You can use a Query like this:

SELECT 
    SUBSTRING_INDEX(str, '-', 1)  as MyMonth,
    SUBSTRING_INDEX(str, '-', -1) as MyYear
FROM month_extract_plan;

answered Feb 24 '20 at 12:08

Bernd Buffen

14,525
2
24
39

nbk · Answer 2 · 2020-02-24T12:10:43.397

0

Use STR_TO:Date

SELECT monthname(STR_TO_DATE('Jan-20','%b-%y')) ,YEAR(STR_TO_DATE('Jan-20','%b-%y'))

gets you

'January 2020

edited Feb 24 '20 at 12:10

answered Feb 24 '20 at 12:06

nbk

45,398
8
30
47

Thank You so much ... I need to get year also – FRECEENA FRANCIS Feb 24 '20 at 12:08
You get a date there you can use every date function of mysql https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date – nbk Feb 24 '20 at 12:11

Extracting Monthname and Year from a string

2 Answers2