MySQL (now() - Interval 1 Month) for this year only -

i need this.

i have following sql statement

select * table  month(orderdate) = month(now() - interval 1 month) 

the problem is returning data last month years... doing wrong or normal issue people face function?

the problem facing if use year(now()) report writing not show data 2016 when hit 2017. i'm trying write 6 month sales history report.

any guidance appreciated.

added information

select * data_wh.salesord_hdr month(orderdate) = month(now() - interval 1 month) 

returns....

'2015-08-14 00:00:00'

try using date_sub between:

select * data_wh.salesord_hdr orderdate between date_sub(now(), interval 1 month) ,                         date_sub(now(), interval 2 month) 

this avoids problem of having deal boundary conditions when using month , year.

edit:

the above query return records order date between 1 , 2 months old. if want identify orders previous calendar month, have bit more work. try query:

select * data_wh.salesord_hdr orderdate >= str_to_date(concat('01-', lpad(month(date_sub(now(), interval 1 month)), 2, '0'), '-', year(date_sub(now(), interval 1 month))), '%d-%m-%y') ,       orderdate <  str_to_date(concat('01-', lpad(month(now()), 2, '0'), '-', year(now())), '%d-%m-%y') 

the strategy here build date boundaries (august 1 , september 1 of 2016, of time of writing answer), using orderdate.

here fiddle showing logic in action:

sqlfiddle