Eloquent model between dates format d.m.Y

Question

I have column "exp_date" with type of string, format of column is d.m.Y. I want to get records between two dates.

I am trying like with this code:

$start = '10.09.2016';
$end = '20.10.2018';

$company->records()->whereBetween('exp_date', [$start, $end])->get();

Possible duplicate of [Laravel $q->where() between dates](http://stackoverflow.com/questions/24824624/laravel-q-where-between-dates) — maiorano84, Jan 09 '16 at 19:24
@maiorano84 not exactly like [Laravel $q->where() between dates]http://stackoverflow.com/questions/24824624/laravel-q-where-between-dates — Jimmy Obonyo Abor, Jan 10 '16 at 02:25

score 2 · Accepted Answer · edited Feb 26 '17 at 08:34

Simply use carbon package which extends the native PHP DateTime class.

EXAMPLE 1 : DATABASE COLUMN TYPE IS SET TO date OR dateTime $start = '10.09.2016'; $end = '20.10.2018';

$start = Carbon::createFromFormat('d-m-Y',  $start)->toDateTimeString(); 
$end = Carbon::createFromFormat('d-m-Y',  $end)->toDateTimeString();

Then create query

$company = Records::where(function($q){
    $q->where('exp_date', '<=',$start );
    $q->where('exp_date', '>', $end);

});

$company->get();

EXAMPLE 2 : DATABASE COLUMN TYPE IS SET TO varchar

Here you will have to first of all covert existing table column via migrations . Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file.

php artisan make:migration update_exp_to_records_table --table=records

Edit the migration file , add below relevant to your table names

Schema::table('records', function ($table) {
    $table->string('expire')->date()->change();
});

Run only this specific migration Then use example 1 above in your controller function.

Yes let me update it when using string/varchar type collumn ,give a few minutes — Jimmy Obonyo Abor, Jan 10 '16 at 18:44

score 0 · Answer 2 · answered Jan 09 '16 at 19:28

With raw query you should use something like this:

SELECT * FROM table_name 
WHERE UNIX_TIMESTAMP(col_name) > UNIX_TIMESTAMP(:start) 
  AND UNIX_TIMESTAMP(col_name) < UNIX_TIMESTAMP(:end)

You don't have to use UNIX_TIMESTAMP() for $start and $end value with MySQL just strtotime() in PHP.

Note: If MySQL do not type your string into unix timestamp, maybe you need to use function CAST().

Documentation:UNIX_TIMESTAMP() in MySQL, strtotime() in PHP

Eloquent model between dates format d.m.Y

2 Answers2