Applying Quarter Function to Month Names Field in MySQL-CodePudding

So I have a field named 'Month' which has month names like January, February, March and so on against per record. This field's data type is currently 'LONGTEXT'.

What I am after is a generated field which looks at this Month column, and outputs its quarter equivalent. So if it is January, the generated field must output '1' in Quarter column. If it is June, the Quarter column will state '2'.

I have worked with this statement so far which creates the generated column but is computing 'NULL' output.

SELECT *, QUARTER(Month) AS Quarter FROM new_schema.group_figures_local

Your help will be much appreciated and apologies if I haven't explained thoroughly. Will be happy to expand on this question if needed.

CodePudding user response：

QUARTER() requires a date or date string, and a month name by itself is not a valid date string.

Use STR_TO_DATE() to convert it to a date. The %M format string parses a month name.

SELECT QUARTER(STR_TO_DATE(Month, '%M')) AS Quarter
FROM new_schema.group_figures_local;

CodePudding user response：

First use CONCAT() so that for each month name you produce a string date like '2000-January-01' and then use STR_TO_DATE() to translate that string to a valid date which you can use with the function QUARTER():

SELECT Month,
       QUARTER(STR_TO_DATE(CONCAT('2000-', Month, '-01'), '%Y-%M-%d')) AS Quarter
FROM new_schema.group_figures_local;

See a simplified demo.