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')";
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.
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?
yes, but it will be ugly, complex, clumsy, and inefficient
seriously, change your datatype to DATE if you possibly can
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?
please repeat the query outside of php, and instead of using php variables, use actual from and to dates
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.
This topic is now closed. New replies are no longer allowed.