CODEWITHSUNDEEP
laravel
Lumi
Lumi

Reputation: 3

Laravel whereBetween issue , wrong records returned

I have two date variables $to and $from.

$from = '2016-06-01';
$to = '2016-06-20';

I am using whereBetween to search between the two dates. If I select for example 1st June to 20th June it only displays records from the 1st to the 19th. How do I include the 20th in that search?

Here is an example of my where clause:

 ->whereBetween('CompletedDate', array($fromDate, $toDate))

Upvotes: 0

Views: 239

Answers (2)

Álvaro Guimarães
Álvaro Guimarães

Reputation: 2222

Use Carbon

You can work better with dates using Carbon.

I came up with a little snippet for you:

<?php
public function index()
{
    $from = Carbon::parse('2016-06-01')->startOfDay();
    $to = Carbon::parse('2016-06-20')->endOfDay();
    $users = User::whereBetween('created_at', [$from, $to])->get();
}

Don't forget to import Carbon:

use Carbon\Carbon;

Upvotes: 0

user1669496
user1669496

Reputation: 33058

The issue is the SQL server reads these dates like so...

$from = '2016-06-01 00:00:00';
$to = '2016-06-20 00:00:00';

If you want to include the latest date, you need to update these accordingly...

$from = '2016-06-01 00:00:00';
$to = '2016-06-20 23:59:59';

If you are using a datepicker, the same principle applies. Your logic would probably look something like the following...

$fromDate = new DateTime(strtotime('2016-06-01'));
$toDate = new DateTime(strtotime('2016-06-20'));

... ->whereBetween('CompletedDate', array($fromDate->format('Y-m-d 00:00:00'), $toDate->format('Y-m-d 23:59:59')));

Upvotes: 2

Related Questions