Ad

Query Builder Where Between Custom Date Format

- 1 answer

My field date (varchar datatype) is in a custom date format it's dd-mm-yyyy.

Example : 01-01-2016

I want to get data between specific dates from a database field date. Let's say input data are stored in variables: startDate & endDate.

I tried with this query but the result are weird.

$query = DB::table('test')->whereBetween('date', array($startDate, $endDate))->get();

I think it fails because I used a custom date format.

How can this be solved?


@updated

let's say i have date like this

29-12-2015
29-12-2015   
29-12-2015
30-12-2015
29-12-2015
01-01-2016
06-01-2016

i set $startDate & $endDate like this

$startDate = "01-12-2015";
$endDate   = "01-01-2016";

it's even not get any result with this script

$query = DB::table('test')->whereBetween('date', array($startDate, $endDate))->get();

but if I using

$startDate = "01-12-2015";
$endDate   = "31-12-2015";

i get all data...which it's wrong result because 2016 data should not in range...it's somehow like not filtered

Ad

Answer

since your date datatype is varchar you can try to use str_to_date function in mysql then before using $starDate and $endDate variable convert it's format first.

Sample code is like this.

$startDate = date("Y-m-d", strtotime("01-12-2015"));
$endDate   = date("Y-m-d", strtotime("31-12-2015"));

$query = DB::table('test')->whereBetween("str_to_date(date, '%d-%m-%Y')", array($startDate, $endDate))->get();

Hope that helps.

Ad
source: stackoverflow.com
Ad