Get Quater using number as input in MySql

All we need is an easy explanation of the problem, so here it is.

i have a column that has month number stored as integer. i would like to create a new column with the quater of each month. is there an simpler way of doing the same, i have to use this in multiple places. so just wanted to check if there is any simpler way of doing this.

currently i am using below to achieve it

case when tt1.resolved_month in (1,2,3) then 1
     when tt1.resolved_month in (4,5,6) then 2
     when tt1.resolved_month in (7,8,9) then 3
     when tt1.resolved_month in (10,11,12) then 4
end as quater

i checked the Quarter function and it doesnt support number as input.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

CEIL function will get the quarter.

SELECT CEIL(resolved_month / 3) AS quarter

Demonstration:

Month   Month/3   Quarter
-------------------------
1       0.3333      1
2       0.6667      1
3       1           1
4       1.3333      2
5       1.6667      2
6       2           2
7       2.3333      3
8       2.6667      3
9       3           3
10      3.3333      4
11      3.6667      4
12      4           4

Working Fiddle

Method 2

Instead of a case expression, you could divide the month by 3 and ceil the result:

SELECT CEIL(resolved_month / 3) AS quarter
FROM   tt1

As @exudong suggested in the comments, it may be a good idea to encapsulate this logic in a function and re-use it wherever needed.

Method 3

You could use the QUARTER() function:

SELECT 
   resolved_month, 
   QUARTER(ADDDATE('2021-01-01',INTERVAL resolved_month-1 MONTH)) as quarter2
FROM months;

Fiddle

The advantage (😉) is that everybody who read this will recognize that the quarter is being calculated.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply