belal belal - 5 months ago 36
MySQL Question

Select date between mysql

I need to select records that are between 2 dates like follow:

select pa_EntryDate , pa_Number , pa_Value , pa_Note
from payments
where (DATE_FORMAT(pa_EntryDate, '%d/%m/%Y')
between '1/6/2016' and '12/6/2016')
and pa_To = 42
and archived=0


The date format in pa_EntryDate column is: "2015-09-10 00:00:00" ..

The results that generated are out of my dates range with some misses:

pa_EntryDate id value
2015-09-10 00:00:00 1356 1031.00
2015-11-12 00:00:00 1564 1111.00
2015-12-10 00:00:00 1644 1138.00
2016-02-11 00:00:00 192 1255.00
2016-03-10 00:00:00 282 1110.00
2016-05-12 00:00:00 467 1141.00

Answer

Convert you date str

select pa_EntryDate , pa_Number , pa_Value , pa_Note 
from payments 
where pa_EntryDate  between STR_TO_DATE('1/6/2016', '%d/%m/%Y') 
                   and STR_TO_DATE('12/6/2016', '%d/%m/%Y') 
and  pa_To = 42 
and archived=0;