CODEWITHSUNDEEP
phplaraveldatebetween
Hardist
Hardist

Reputation: 1983

Laravel search between dates no results when dates are the same (get results from today)

I have an appointments table in which I store the appointment start date and time in a datetime field. I have a bunch of methods which I use to get appointments that start on a specific date or between two given dates. For example:

Calendar Class:

public function today()
{
    $start = Carbon::now()->hour('00')->minute('00')->second('00');
    $end = Carbon::now()->hour('23')->minute('59')->second('00');

    return Appointment::getAppointments($this->start, $this->end);
}

// public function tomorrow();
// public function thisMonth();

Appointment Model:

public static function getAppointments($start, $end)
{
    return static::whereBetween('date', array(
        $start->format('Y-m-d H:i'),
        $end->format('Y-m-d H:i')
    ))->get();
}

As you can see I need to set the hour, minutes and seconds, because otherwise it will not return any results, if the start and end date are the same. Is it possible to simply do the following and still get the results from that date?

public function today()
{
    $start = Carbon::now();
    $end = Carbon::now();

    return Appointment::getAppointments($this->start, $this->end);
}

public static function getAppointments($start, $end)
{
    return static::whereBetween('date', array(
        $start->format('Y-m-d'),
        $end->format('Y-m-d')
    ))->get();
}

Upvotes: 0

Views: 562

Answers (2)

Rob Fonseca
Rob Fonseca

Reputation: 3849

I believe the problem is your date field most likely has a time component that you need to truncate if you are to compare if it is between dates.

Make sure to import the db namespace at the top of your controller

use DB;

This should convert the date to Y-m-d so it will fall between your dates

public static function getAppointments($start, $end)
{
    return static::whereBetween(DB::raw('CAST(date as DATE)', array(
        $start->format('Y-m-d'),
        $end->format('Y-m-d')
    ))->get();
}

Upvotes: 1

Ghitu Ilie-Alin
Ghitu Ilie-Alin

Reputation: 139

Try the following code

return static::whereBetween(Db:raw('DATE(date)'), array(
    $start->format('Y-m-d'),
    $end->format('Y-m-d')
))->get();

This will cast the DATETIME field to DATE

Upvotes: 1

Related Questions