Mysql query question

Question

I have 2 tables:

Customer: customerid - int, pri-key,auto fname - varchar sname -varchar housenum - varchar street -varchar

Items: itemid - int,pri-key,auto type - varchar collectiondate - date releasedate - date customerid - int

EDIT I need a query which will get me all items that have a releasedate of 3 days into the future, including today

i.e The query should return customerid,fname,sname,street,housenum,type,releasedate for all items which have releasedate of today or 3 days into the future.e.g, if todays date was 2010-12-24, and an item has release date of 2010-12-25 then it would be returned ion the query.

The idea is that if an item is due for release (today or 3 days ahead of today) then it will show this. thanks in advance

Quassnoi · Accepted Answer · 2010-12-24 13:44:38Z

1

SELECT  c.customerid, c.fname, c.sname, c.street, c.housenum, i.type, i.releasedate
FROM    items i
JOIN    customers c
ON      c.customerid = i.customerid
WHERE   i.releasedate >= CURDATE()
        AND i.releasedate < CURDATE() + INTERVAL `4` DAY

Note the use of two comparisons (strict and loose) instead of BETWEEN, and 4 days instead of 3.

This means that if you run this query on Jan 1st, it will return you the items with release date greater or equal than Jan 1st, but strictly less that Jan 5th.

This means that any record with date part of 1, 2, 3, 4 of January will make it to your query.

edited Dec 24, 2010 at 13:44

answered Dec 24, 2010 at 13:22

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

The Scrum Meister · Accepted Answer · 2010-12-24 04:42:17Z

1

SELECT c.customerid, c.fname, c.sname, c.street, c.housenum, i.type, i.releasedate 
FROM Items i JOIN Customer c ON i.customerid  = c.customerid 
WHERE i.releasedate BETWEEN NOW() - INTERVAL 3 DAY AND NOW()

answered Dec 24, 2010 at 4:42

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

3 Comments

The Scrum Meister Over a year ago

If you want to get from 3 days prior to the current date (today 12:00 AM) use CURDATE() instead of NOW()

brux Over a year ago

Sorry I have edited my post, I need all items which have a releasedate between TODAYs date and 3 days ahead of today. I edited your query so that it is .. BETWEEN NOW() + INTERVAL 3 DAY AND NOW() ... but it returns no rows :s

The Scrum Meister Over a year ago

Switch it to BETWEEN NOW() AND NOW() + INTERVAL 3 DAY , the lower part must come first

Collectives™ on Stack Overflow

Mysql query question

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related