0

I had a date field like 2014-12-30 and I did something like EXTRACT(YEAR_MONTH FROM date) as yr_month which returned a value like 201412.

I'd like to retrieve the month from this new value, reverse engineer it back to the month. I tried MONTH(yr_mnth) and also MONTH(RIGHT(yr_mnth, 2)).

Any thoughts on how to retrieve the month from this field?

Joseph Erickson
  • 938
  • 3
  • 8
  • 24
  • Any reason not to use MONTH()? – Niko Hujanen Apr 27 '16 at 19:50
  • The original date field isn't what I want. The extract was done on `MIN(date)` denoting a 'cohort' in which the customer became a customer. I can go back and make some edits, but the data is aggregating nicely BECAUSE of this EXTRACT() on month and year. I'd prefer to find a solution on working with EXTRACT unless its not possible. – Joseph Erickson Apr 27 '16 at 19:52
  • Perhaps this could help you on your journey: STR_TO_DATE(yr_mnth,'%Y%m') – Niko Hujanen Apr 27 '16 at 19:59
  • Why do you need to call `MONTH()`. Doesn't `RIGHT(yr_month, 2)` return what you want? – Barmar Apr 27 '16 at 20:30

1 Answers1

2

Try the following:

SELECT MONTHNAME(STR_TO_DATE(RIGHT(yr_mnth, 2), '%m'));

This will get you the monthname from the month number. As you can see here: MySQL MONTHNAME() from numbers

Community
  • 1
  • 1
fqhv
  • 1,191
  • 1
  • 13
  • 25