CODEWITHSUNDEEP
mysqlsql
Sparki
Sparki

Reputation: 161

MySQL Select on separate year and month cols

I cannot get my head around this one...

Create table and dump some data in it

CREATE TABLE `_xxx` (
  `x` DECIMAL(10,5) NULL DEFAULT NULL,
  `x_year` INT(4) NULL DEFAULT NULL,
  `x_month` INT(2) NULL DEFAULT NULL);

insert into  _xxx (x, x_year, x_month) values (10,2010,1);
insert into  _xxx (x, x_year, x_month) values (20,2010,2);
insert into  _xxx (x, x_year, x_month) values (30,2010,3);
insert into  _xxx (x, x_year, x_month) values (40,2010,4);
insert into  _xxx (x, x_year, x_month) values (50,2010,5);
insert into  _xxx (x, x_year, x_month) values (60,2010,6);
insert into  _xxx (x, x_year, x_month) values (70,2010,7);
insert into  _xxx (x, x_year, x_month) values (80,2010,8);
insert into  _xxx (x, x_year, x_month) values (90,2010,9);
insert into  _xxx (x, x_year, x_month) values (11,2011,1);
insert into  _xxx (x, x_year, x_month) values (21,2011,2);
insert into  _xxx (x, x_year, x_month) values (31,2011,3);
insert into  _xxx (x, x_year, x_month) values (41,2011,4);
insert into  _xxx (x, x_year, x_month) values (51,2011,5);
insert into  _xxx (x, x_year, x_month) values (61,2011,6);
insert into  _xxx (x, x_year, x_month) values (71,2011,7);
insert into  _xxx (x, x_year, x_month) values (81,2011,8);
insert into  _xxx (x, x_year, x_month) values (91,2011,9);
insert into  _xxx (x, x_year, x_month) values (12,2012,1);
insert into  _xxx (x, x_year, x_month) values (22,2012,2);
insert into  _xxx (x, x_year, x_month) values (32,2012,3);
insert into  _xxx (x, x_year, x_month) values (42,2012,4);
insert into  _xxx (x, x_year, x_month) values (52,2012,5);
insert into  _xxx (x, x_year, x_month) values (62,2012,6);
insert into  _xxx (x, x_year, x_month) values (72,2012,7);
insert into  _xxx (x, x_year, x_month) values (82,2012,8);
insert into  _xxx (x, x_year, x_month) values (92,2012,9);

Now trying to select all before a date and all after a date (everything between two dates)...

SELECT * FROM _xxx WHERE x_year >= 2011 AND x_month >= 7

of course this not correct...

any pointers?

Thanks.

Upvotes: 0

Views: 82

Answers (2)

Mihai
Mihai

Reputation: 26804

It`s awkward but it works

SELECT *,DATE(CONCAT(x_year,'-',x_month,'-',01))dateColumn 
FROM _xxx 
HAVING dateColumn BETWEEN '2011-04-01' AND '2012-06-01'

Fiddle

You can use the same formula to create a DATE field so you can index it.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

This is a hint, not an answer - but I wanted to take advantage of additional formatting options...

SELECT (2013,06) >= (2012,08) AND (2013,06) <= (2014,05) x;
+---+
| x |
+---+
| 1 |
+---+

Upvotes: 0

Related Questions