CODEWITHSUNDEEP
mysqlsqlselectbetween
Shri
Shri

Reputation: 731

select records between two months with year difference

I have a table which contain records of people according to month and year. table have multiple columns in which there are two columns which are fldmonth & fldyear, which contain month no and year respectively.

Now I want to fetch data between months of different year. (E.g. 3-2012 to 6-2013)

I am using following query, but not getting proper record.

SELECT * FROM 'table' WHERE
user_id = 'id' AND
STR_TO_DATE(CONCAT('fldyear', 'fldmonth', '01'), '%Y%m%d') BETWEEN
STR_TO_DATE(CONCAT('2012', '3', '01'), '%Y%m%d') AND 
STR_TO_DATE(CONCAT('2013', '6','01'), '%Y%m%d');

Table Schema :

user_id varchar(100), fldmonth smallint(2), fldyear mediumint(4)

(table name & userid given here are just for example)

Please need help.

Note: I used %c also in date format because month are in 1,2,..12 format. But still am getting empty result set

Upvotes: 2

Views: 2596

Answers (3)

Amarnath Balasubramanian
Amarnath Balasubramanian

Reputation: 9460

SELECT * FROM tbl 
WHERE USERID=1 and
STR_TO_DATE(CONCAT(fldyear,'-',LPAD(fldmonth,2,'00'),'-',LPAD(fldate,2,'00')), '%Y-%m-%d')
BETWEEN
STR_TO_DATE(CONCAT(2012,'-',LPAD(03,2,'00'),'-',LPAD(01,2,'00')), '%Y-%m-%d') AND 
STR_TO_DATE(CONCAT(2013,'-',LPAD(06,2,'00'),'-',LPAD(01,2,'00')), '%Y-%m-%d');

Working Fiddle

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Remove Single quote from column names.

Try this: 

SELECT * 
FROM table 
WHERE user_id = 'id' AND
      STR_TO_DATE(CONCAT(fldyear, fldmonth, '01'), '%Y%c%d') BETWEEN '2012-03-01' AND '2013-06-01';

Upvotes: 1

zzlalani
zzlalani

Reputation: 24344

Saharsh Shah is right, but what you need is to add brackets () too in your condition

change the condition with 

WHERE user_id = 'id' AND
( STR_TO_DATE(CONCAT(fldyear, fldmonth, '01'), '%Y%m%d')
BETWEEN '2012-03-01' AND '2013-06-30' );

Upvotes: 0

Related Questions