CODEWITHSUNDEEP
sqlms-accessms-access-2010
imstuck
imstuck

Reputation: 13

select data by month sql

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.

Upvotes: 1

Views: 186

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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())

Upvotes: 3

Lagastic
Lagastic

Reputation: 93

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

Upvotes: 1

Related Questions