SQL Between Dates Syntax

Question

I have a query that needs to return records where the ShipDate is between a range of dates. The following query works:

$query = "SELECT ECCustomerOrder.ShipDate, 
ECCustomers.Customer, 
ECCustomerOrder.PO, 
ECCustomerOrder.TotalCases, 
ECCustomerOrder.order_number
FROM ECCustomerOrder INNER JOIN ECCustomers ON ECCustomerOrder.ID = ECCustomers.ID
WHERE ECCustomerOrder.ShipDate >= $todayMinus";

$todayMinus is a php variable set earlier in the script that takes the current date and subtracts 15 days. What I can't get to work is the following:

$query = "SELECT ECCustomerOrder.ShipDate, 
ECCustomers.Customer, 
ECCustomerOrder.PO, 
ECCustomerOrder.TotalCases, 
ECCustomerOrder.order_number
FROM ECCustomerOrder INNER JOIN ECCustomers ON ECCustomerOrder.ID = ECCustomers.ID
WHERE ECCustomerOrder.ShipDate BETWEEN $todayMinus AND $todayPlus";

What is the proper syntax for this SQL query to return the records between the ShipDates of $todayMinus and $todayPlus?

Is this MySQL?, do you need to include results for today?, why is it not working? — Lamak
– Lamak, Commented Feb 19, 2013 at 19:13

Marc B · Accepted Answer · 2013-02-19 19:15:18Z

3

assuming you're using plain dates, you'll have to quote them.

e.g.

ShipDate BETWEEN 2013-01-01 AND 2013-02-19

is incorrect, because 2013-01-01 is a substraction, you're actually doing

ShipDate BETWEEN 2011 AND 1992

try

ShipDate BETWEEN '$todayMinus' AND '$todayPlus'

answered Feb 19, 2013 at 19:15

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

Sterve Over a year ago

That's all it took, Marc. Thanks for the tip.

Collectives™ on Stack Overflow

SQL Between Dates Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related