Query data base on date for Editor

newred

anyone come across this? my Date column is in varchar and in my editor i try to query like this

if (isset( $_GET['Start'])) {
  $editor
  ->where('timesheets.UserId',$_GET['UserId'] )
  ->where('str_to_date(timesheets.Date,"%d-%M-%Y")','str_to_date("'.$_GET['Start'].'","%d-%M-%Y")',">=" )
  ->where('str_to_date(timesheets.Date,"%d-%M-%Y")','str_to_date("'.$_GET['End'].'","%d-%M-%Y")',"<=" );
}

I test the query in phpmyadmin, it works but in editor it is not. Then i try

if (isset( $_GET['Start'])) {
  $editor
  ->where('timesheets.Date',$_GET['Start'],">=" )
  ->where('timesheets.Date',$_GET['End'],"<=" );
}

this one return data but the it only compare the day not month . e.g: when i select between 01-Nov-2016 to 30-Nov-2016, the data fall in 01-31 Dec-2016 appear also as long as the day is in between 01 and 31. kindly advise. i wanted to make the first option works but no clue.

newred

Note : all my date is in d-M-Y ( e.g:08-Dec-2016 ) format

allan

Is your date column in the table actually a date data type? If so, just use an ISO8601 formatted date and your database will do the rest for you - no need to format the date column. If you are submitting the value in something other than ISO8601 use date and strtodate to convert it.

Allan

newred

nope, my date column is string column

allan

Ouch . In that case you'd need to do something like in this binding data example. Basically use an anonymous function for the where condition which will give you access to the underlying query object and the ability to have it not bind the value as a string (i.e. let it be used as a function).

If you can, I'd recommend you change the SQL column type, otherwise you might run into performance issues with larger data sets.

Allan