0

I am trying to select between two dates in PHP and SQL. However, my PHP dates are printed like this:

July 1, 2017

and

July 30, 2017

The problem is, that in the database I am selecting from, the dates is stored like this yyyy-MM-DD: 2017-07-01 and 2017-07-30

I am trying to make a query, to select between two dates:

SELECT dl.list_id, dl.id, dl.date, dl.list_name, dl.list_supplier, dls.reference, dls.mawb, dls.shipment, dls.kg
FROM driving_lists dl LEFT JOIN
driving_list_shipments dls
ON dl.list_id = dls.list_id
WHERE dl.deleted=0 AND dls.kg > 0 AND dl.date between 'FORMAT(July 01, 2017,'yyyy-MM-DD')' and 'FORMAT(July 30, 2017,'yyyy-MM-DD')'
ORDER BY dl.list_id

So, the problem is with this:

dl.date between 'FORMAT(July 01, 2017,'yyyy-MM-DD')' and 'FORMAT(July 30, 2017,'yyyy-MM-DD')'

How can I select between two dates by converting it to the format, that the database uses?

oliverbj
  • 5,771
  • 27
  • 83
  • 178

2 Answers2

0

You should use str_to_date() if you want to do the transformation in the database:

WHERE dl.deleted = 0 AND dls.kg > 0 AND
      dl.date between str_to_date('July 01, 2017', '%M %d, %Y') and 
                      str_to_date('July 30, 2017', '%M %d, %Y')

Note: dl.date should be stored as a date in the database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try with date() php function
Like this

$date1='July 30, 2017';
$date1=date('Y-m-d',strtotime($date1));
$date2='July 1, 2017';
$date2=date('Y-m-d',strtotime($date2));

If you print $date1 and $date2 it will look like

"2017-07-30"

"2017-07-01"
Bibhudatta Sahoo
  • 4,808
  • 2
  • 27
  • 51