CODEWITHSUNDEEP
mysqllaraveleloquent
jestin-g
jestin-g

Reputation: 41

MySQL query with DATE_FORMAT() to Laravel Querybuilder (or Eloquent)

I'm trying to get records from a table on a specific day. Dates's format: datetime (YYYY-MM-DD HH:II:SS)

I'm trying to translate a MySQL query to Laravel query builder. But I don't know how to deal with the DATE_FORMAT().

Here is MySQL query

SELECT * FROM events
JOIN customers ON events.id_customer = customers.id
WHERE start_date > DATE_FORMAT("2017-06-15", "%Y-%m-%d %H:%i:%s")
AND end_date < DATE_FORMAT("2017-06-16", "%Y-%m-%d %H:%i:%s")
;

In my controller I get a date (string) that I put into a DateTime

$date_datetime = new \DateTime(Input::get('date_string'));

And then change its format

$query_date = date_format($date_datetime, 'Y-m-d H:i:s');

I've tried the following code, but it is obviously not working

$pdf_events = DB::table('events')
->join('customers', 'events.id_customer', '=', 'customers.id')
->select(...)
->whereRaw("start_date > DATE_FORMAT($query_date, '%Y-%m-%d %H:%i:%s')")
->whereRaw("end_date < DATE_FORMAT($query_date, '%Y-%m-%d %H:%i:%s')+1")
->get();

Upvotes: 0

Views: 2835

Answers (4)

Aashif Ahamed
Aashif Ahamed

Reputation: 1132

use Datetime;

$end_date1 = $request->input('end_date');
$EndDateTime = DateTime::createFromFormat('d/m/Y H:i:s', $end_date1);
$end_date = $EndDateTime->format('Y-m-d H:i:s');

try like this its work for me

Upvotes: 0

Zendy
Zendy

Reputation: 774

I strongly recommend using Carbon for handling DateTime in Laravel since it has support out of the box.

Now, how to use it to resolve your problem?

First, you need to convert your input into Carbon object.

$start_date = Carbon::parse(Input::get('date_string'));

// Assuming you want the end date 1 day later at same time
$end_date = $start_date->addDay();


// You could change the format with format() but in this case we don't need to

Then in your Eloquent, you can use the Carbon Datetime object to query using where()

$pdf_events = DB::table('events')
->join('customers', 'events.id_customer', '=', 'customers.id')
->select(...)
->where("start_date", ">", $start_date)
->where("end_date", "<", $end_date)
->get();

Upvotes: 1

ako
ako

Reputation: 2136

You don't need DATE_FORMAT, Try this:


$query_start_date = \Carbon\Carbon::createFromFormat("Y-m-d H:i:s", request()->date_string);

// Add 1 day to the date
$query_end_date = $query_start_date->addDay();



$pdf_events = DB::table('events')
    ->join('customers', 'events.id_customer', '=', 'customers.id')
    ->where(
        [
            ["start_date", ">", $query_start_date],
            ["end_date", "<", $query_end_date]
        ]
    )->get();

Upvotes: 0

Manoj Vadehra
Manoj Vadehra

Reputation: 846

You can pass Carbon (doc) objects to compare dates. For example:

->where('start_date', '=', Carbon::now())

You can build a custom Carbon object for a custom date-time to pass into the where clause.

Upvotes: 1

Related Questions