CODEWITHSUNDEEP
mysqldatetime
Megha Verma
Megha Verma

Reputation: 151

How can I add days to a date in MYSQL in a query

I am trying to add 5 days to a date in MYSQL in a query. This is what I have done:

SELECT * FROM sales INNER JOIN partner on user_id = idpartner WHERE DATE((end_date) + 5) >= DATE(NOW()) ORDER BY end_date ASC LIMIT 0,50000

But this is not showing the list of sales which has ended. Can someone please tell me where I am making a mistake.

Upvotes: 1

Views: 7870

Answers (3)

RohitS
RohitS

Reputation: 1046

You can try DATE_ADD() here is the Link

Select DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) FROM DUAL

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

It looks like you want rows where end_date is later than five days ago.

The best way to get that is with

 WHERE end_date >= CURDATE() - INTERVAL 5 DAY

The business of adding integers to dates doesn't work in MySQL (it's an Oracle thing). So you need to use the INTERVAL n unit syntax.

You'll notice that my WHERE clause above is functionally equivalent to 

 WHERE DATE(end_date) + INTERVAL 5 DAY >= DATE(NOW())

But, the first formulation is superior to the second for two reasons.

  1. if you mention end_date in a WHERE clause without wrapping it in computations, your query can exploit an index on that column and can run faster.
  2. DATE(NOW()) and CURDATE() both refer to the first moment of today (midnight). But CURDATE() is a bit simpler.

Upvotes: 6

Tomer Shay
Tomer Shay

Reputation: 801

To fix the original query, you can use DATE_ADD with the INTERVAL keyword:

SELECT 
    *
FROM
    sales
        INNER JOIN
    partner ON user_id = idpartner
WHERE
    DATE_ADD(end_date, INTERVAL 5 DAY) >= DATE(NOW())
ORDER BY end_date ASC
LIMIT 0 , 50000

Said that, I wouldn't recommend applying functions such as DATE_ADD on columns, as it means that the database won't be able to use an index on end_date. Therefore, I would modify the query to:

SELECT 
    *
FROM
    sales
        INNER JOIN
    partner ON user_id = idpartner
WHERE
    end_date <= DATE_ADD(DATE(NOW()), INTERVAL 5 DAY)
ORDER BY end_date ASC
LIMIT 0 , 50000

As you can see, in the second alternative all functions are applied on constants and not on columns (end_date).

Upvotes: 0

Related Questions