SQL查询，以检查从今天起2周后的日期

We have a booking form, where users can book for several tours and for several tour dates. An order is created after they book and is set to 'pending' state. We want to process the order exactly 2 weeks before the tour date.

We wrote a cron job function and its intended purpose is to get all the orders which are in 'pending' state and there is exactly 2 weeks time for the tour date.

This is the query

$orders = $wpdb->get_results("SELECT o.*, b.departure_point, b.tour_date, b.tour_id FROM ".CT_TOUR_BOOKINGS_TABLE." b JOIN ".CT_ORDER_TABLE." o ON o.id = b.order_id WHERE o.status = 'pending' AND NOW( ) + INTERVAL 2 WEEK = b.tour_date");

The above line is not working, as we did some trial bookings. Is there anything wrong with the statement

"NOW( ) + INTERVAL 2 WEEK = b.tour_date"

Thanks for help.

Try now()+14? I tried something like this on oracle using sysdate+14 and it worked.

you should user this to get two week interval

DATE_SUB(curdate(), INTERVAL 2 WEEK)

you can change your date format as you need

"NOW( ) + INTERVAL 2 WEEK = b.tour_date"

should be

"DATE_ADD(NOW(), INTERVAL 2 WEEK) = b.tour_date"

instead

If tour_date is a datetime field then it won't match NOW( ) + INTERVAL 2 WEEK as it will try to compare hours, minutes and seconds too. What you need to compare is date part only, try the following:

AND DATE(b.tour_date) =  DATE(NOW() + INTERVAL 2 WEEK)

Here's the documentation on MySQL's DATE function.

You can try with DATEDIFF instead:

"SELECT o.*,
        b.departure_point,
        b.tour_date,
        b.tour_id 
   FROM ".CT_TOUR_BOOKINGS_TABLE." b 
   JOIN ".CT_ORDER_TABLE." o 
     ON o.id = b.order_id 
  WHERE o.status = 'pending' 
    AND DATEDIFF(b.tour_date, today) = 14"

Try this.

$orders = $wpdb->get_results("SELECT o.*, b.departure_point, b.tour_date, b.tour_id FROM ".CT_TOUR_BOOKINGS_TABLE." b JOIN ".CT_ORDER_TABLE." o ON o.id = b.order_id WHERE o.status = 'pending' AND ((GETDATE()+14) = b.tour_date");

'now' is not a recognized built-in function name. Instead, use GetDate().

For reference https://www.sqlservercentral.com/Forums/Topic183904-8-1.aspx