Laravel - query data from mysql between dates

Question

I am having an issue getting rows from my table using the below query, the expected behaviour is:

user selects start/end date in a form, hits submit and the data passes to the function & it is then used in the query.

The data from the form is coming in fine & I have verifed the dates are coming into the query, but for some reason the query always returns zero results? When I strip out the WHERE statement for the dates, the query works fine & produces results, so the issue is to do with the dates.

$fromDate = $request->fromDate;
$toDate = $request->toDate;

$data = cc_table::all('*')
    ->where('link', '1')
    ->where('created_at', '>', $fromDate)
    ->where('created_at', '<', $toDate)
    ->toArray();

The dates from the form are formatted as dd/mm/yyyy, and the created_at date is a timestamp, however for testing I did change the created_at to be DATE (dd/mm/yyyy) however the query still returns zero rows.

FYI - I am just using jquery datepicker on my form:

 $(function() {
    $( "#fromDate" ).datepicker();
  });
  </script>

why not use yyyy-mm-dd all the way through?

Strawberry
– Strawberry

2016-06-23 00:37:28 +00:00
Commented Jun 23, 2016 at 0:37 — Strawberry
– Strawberry, Commented Jun 23, 2016 at 0:37

Raphael Rafatpanah · Accepted Answer · 2016-12-16 12:18:05Z

2

Have a try with this:

$fromDate = Carbon::parse($request->input('fromDate'))->format('Y-m-d');
$toDate = Carbon::parse($request->input('toDate'))->format('Y-m-d');
$date_range = [$fromDate . ' 00:00:00', $toDate . ' 23:59:59'];

$data = cc_table::where('link', '1')
->whereBetween('created_at', $date_range)
->get();

The format of $date_range will depend on the type of your MySQL column.

Note: be sure to use Carbon\Carbon; at the top of the script with the above code snippet.

edited Dec 16, 2016 at 12:18

answered Jun 23, 2016 at 0:46

Raphael Rafatpanah

20.2k28 gold badges106 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

just_chris Over a year ago

That is brilliant and worked perfectly, thanks for your help on this.

just_chris Over a year ago

Also, thanks for the 'whereBetween' - I overlooked this on the laravel Docs. thanks

Collectives™ on Stack Overflow

Laravel - query data from mysql between dates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related