CODEWITHSUNDEEP
mysqlsqlsql-serverdatewhere-clause
GPiter
GPiter

Reputation: 799

Sql get data based on month an year that are numbers

I have a table with this data : 

id              quantity              month                  year
1                10                   12                    2020
2                78                   3                     2019
3                67                   4                     2020
4                78                   3                     2020
5                56                   2                     2020
6                34                   4                     2017
7                56                   2                     2017

I want to get all data's from 2017, month 3 till 2020 month 3 My sql look like : 

where (dc.year >= 2017 and dc.month >= 3) and (dc.year <= 2020 and dc.month <= 3)
GROUP BY dc.year, dc.month

this sql get only data for 2017,3 and 2018, 3 and 2019,3 and 2020,3 But I need the whole interval. If I put

where dc.year >= 2017 and dc.year <= 2020
GROUP BY dc.year, dc.month

Getting all datas but I need data from 2017 month 3 -----> 2020 month 3 (all interval)

Upvotes: 0

Views: 28

Answers (2)

GMB
GMB

Reputation: 222402

You could simply build dates from these numbers and use them for comparison. I think that it makes the intent clearer, and really is in the spirit of your logic (which is to filter by date):

In SQL Server:

where datefromparts(year, month, 1) between '2017-03-01' and '2020-03-01'

In MySQL:

where concat_ws('-', year, month, 1) between '2017-03-01' and '2020-03-01'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method uses arithmetic:

where dc.year * 12 + dc.month between 2017 * 12 + 3 and 2020 * 12 + 3

Alternative, you can use a more complex expression:

where (dc.year = 2017 and dc.month >= 3) or
      (dc.year > 2017 and dc.year < 2020) or
      (dc.year = 202 and dc.month <= 3)

Upvotes: 2

Related Questions