Retrieve data from MySQL between two date ranges

Databases
1

hi all,

I have to retrieve data based on two date ranges, please help, my current Query should retrieve all records but it displays no records?

on submit, I have 2 date ranges in the $_POST data : $datefrom & $dateto in this format = 01-01-2014 ( dd/mm/yyyy )

in the table I am trying to query, I have a column called e_date ( dd/mm/yyyy ) saved as a string.

this is my query which is not retrieving any info nor generating any errors, I can’t figure this one out, please help

my query :

$query="SELECT * from MYTABLE where client_id=$client AND STR_TO_DATE(e_date, '%d/%m/%Y') BETWEEN STR_TO_DATE($datefrom, '%d-%m-%Y') AND STR_TO_DATE($dateto, '%d-%m-%Y')";
2

Though you may feel more comfortable seeing dates in dd-mm-yyyy format, it isn’t the best for code working with dates.
Try yyyy-mm-dd instead.

3

Hi,

thanks for the info, before I change the date formats in the database, is there a way to make the current query work as is?

thanks

4

yes, but it will be ugly, complex, clumsy, and inefficient

seriously, change your datatype to DATE if you possibly can

5

HI

ok, I have changed the datatype to DATE and changed my query to :

$query="SELECT * from MYTABLE WHERE client_id=$client AND e_date BETWEEN $datefrom AND $dateto";

but it still does not work, the default date format now is yyyy-mm-dd, but with this query, still no results? I also added dummy records with correct date format, still nothing?

6

please repeat the query outside of php, and instead of using php variables, use actual from and to dates

7

hi

Ok, just fixed it now, it works 100%, I forgot to inclose the $datefrom & $dateto data with ‘’, works like a charm now, thank you for your help, and from now on, will save dates as DATE types, not strings.