CODEWITHSUNDEEP
mysql
Whip
Whip

Reputation: 2232

Mysql - Get season from current month

I have the following table of seasons:

| id  | name   | start_month | end_month |
------------------------------------------
| 101 | Summer | 12          | 2         |
| 102 | Winter | 6           | 8         |
| 103 | Spring | 9           | 11        |
| 104 | Fall   | 3           | 5         |

I need to get the season by month. Say current month is 2 (February), I want Summer to be the output.

I can get other seasons to work by simply having the where condition start_month >= 4 and end_month <= 4. But this won't work with Summer since the season crosses into next year.

What do I have to do to handle the case of Summer?

One solution I thought was to use dates instead of month number like 1980-12-01 and use between function but it gets a bit complicated for the user end.

It'd be great if it could work with just month numbers.

Upvotes: 0

Views: 160

Answers (1)

slaakso
slaakso

Reputation: 9080

You could do:

(month(d) between start_month and end_month) or 
      (start_month>end_month and (month(d)>=start_month or month(d)<=end_month))

See db-fiddle

Upvotes: 2

Related Questions