devan21 — 2014-06-01T10:57:18-04:00 — #1
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')";
mittineague — 2014-06-01T12:46:49-04:00 — #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.
devan21 — 2014-06-01T12:52:05-04:00 — #3
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?
r937 — 2014-06-01T12:55:20-04:00 — #4
yes, but it will be ugly, complex, clumsy, and inefficient
seriously, change your datatype to DATE if you possibly can
devan21 — 2014-06-01T13:19:41-04:00 — #5
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?
r937 — 2014-06-01T13:23:24-04:00 — #6
please repeat the query outside of php, and instead of using php variables, use actual from and to dates
devan21 — 2014-06-01T13:28:55-04:00 — #7
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.