select data by month sql

Question

I have a table consisting of:

Product_ID (number),
Product_Name (varchar),
Expired_Date (datetime)

I need to select the products which will be expired on the next six months. I already created a statement, it doesn't give me an error message, but I guess it's not working because it returns no result.

The Statement:

SELECT prod.Product_ID, prod.Product_Name, prod.Expired_Date
WHERE (month(prod.expired_date)) - month(date()) = 6

Where did I go wrong?

Additional question :

I want the records that will be expired on the sixth month from this month would be selected as well. For example, six months from now is January 2016. One of the record has expired_date in January 16, 2016 and today is July 06, 2015. There are few days remaining until it become a whole six months, so this record is not selected. What should I do to select all of the records that will be expired in January?

Note: I'm working with MS Access.

Gordon Linoff · Accepted Answer · 2015-07-07T02:30:41.767

3

Instead, you want to do something like this:

where prod_expired_date < date_add(curdate(), interval 6 month)

month() returns the month number, 1 to 12. That does not do what you want.

I should add, if you only want things that will expire in the future:

where prod_expired_date >= curdate() and
      prod_expired_date < date_add(curdate(), interval 6 month)

EDIT:

In MS Access, the following should work:

where prod_expired_date < DATEADD("m", 6, now())

EDIT II:

That is a bit trickier. You can go to the last day of the previous month and add 7 months:

where prod_expired_date < DATEADD("m", 7, DATEADD("d", -DAY(now()), now())

edited Jul 07 '15 at 02:30

answered Jul 05 '15 at 17:34

Gordon Linoff

1,242,037
58
646
786

hello, thanks for your suggestion. I'm sorry, I put the wrong tag. It shouldn't be 'mysql' since I work with ms. access query right now. Isn't the 'interval 6 month' only for mysql? because it gave me an error message (CMIIW) – imstuck Jul 05 '15 at 17:57
1

@imstuck Try to stick to one question per post, it makes things much simpler and easier for users to find in the future. I would just accept GordonLindoff's answer and post a new question :-) – Newd Jul 07 '15 at 15:13
@Gordon It works really fine now. Thank you so much, Sir. It's really enlightening, I learned so many new things about this topic by your answers. Have a nice day! – imstuck Jul 08 '15 at 04:31
@Newd oops, I'm sorry for the inconvenience! I did some revision to the post by adding the additional questions, so it looks less confusing now (I hope). Thanks for your suggestion :) – imstuck Jul 08 '15 at 04:43
@imstuck I would recommend reverting the question back to the original and then posting an entirely new question. I can say with some degree of certainty that your additional question won't be seen or answered :) – Newd Jul 08 '15 at 12:13

Lagastic · Answer 2 · 2015-07-05T17:42:27.077

1

SELECT prod.Product_ID, prod.Product_Name, prod.Expired_Date FROM table WHERE prod.Expired_Date BETWEEN '2015-07-05 00:00:00' AND '2016-01-05 23:59:59'
This should work if you us hh:m:ss format dates.

or

SELECT prod.Product_ID, prod.Product_Name, prod.Expired_Date FROM table WHERE prod.Expired_Date BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 MONTH

edited Jul 05 '15 at 17:42

answered Jul 05 '15 at 17:37

Lagastic

93
1
11

he is asking about expires in next 6 mnts not in between.`I need to select the products which will be expired on the next six months` – Vikram Jul 05 '15 at 17:41
No problem :) didn't know you were using MS access though. – Lagastic Jul 08 '15 at 12:04

select data by month sql

2 Answers2