WhereBetween dates with cast in laravel

Question

Recently I've used whereBetween to get the date range of both dates but the result is not inclusive in same date. So I come out with this query.

SELECT *
FROM student
WHERE cast(created_at as date) BETWEEN '2019-07-21' AND '2019-07-21'

Now, since I'm just new in laravel. How can I adopt this query to laravel?

Here is my code in laravel:

$student = $this->student
->whereBetween('created_at', ['2019-07-21', '2019-07-21'])
->select('*')
->get()

I dont know how to add cast in whereBetween. Can somebody help me?

Can you show us sample data from the `created_at` column? Are you storing your dates as _text_? — Tim Biegeleisen, Jul 21 '19 at 03:13
@TimBiegeleisen yes ```created_at``` data type is ```datetime``` — Rubics, Jul 21 '19 at 13:59
Then there is no reason why my answer should not be working. — Tim Biegeleisen, Jul 21 '19 at 14:18
What I want is to get the data within the same date. Try run your query with the same date. — Rubics, Jul 21 '19 at 14:54

score 2 · Accepted Answer · answered Jul 21 '19 at 02:54

I suggest avoiding using the my MySQL CAST() construct and insted include the edge times in both dates. The query will perform better as well.

$student = $this->student
->whereBetween('created_at', ["$from 00:00:00", "$to 23:59:59"])
// If working with Carbon instances
// ->whereBetween('created_at', [$from->format('Y-m-d 00:00:00', $to->format('Y-m-d 23:59:59'])
->select('*')
->get()

score 1 · Answer 2 · answered Jul 21 '19 at 03:00

1

You may actually phrase your current query using date literals, and completely avoid the cast to date:

SELECT *
FROM student
WHERE created_at >= '2019-07-21' AND created_at < '2019-07-22'

Laravel code:

$student = $this->student
    ->where('created_at', '>=', '2019-07-21')
    ->where('created_at', '<', '2019-07-22')
    ->select('*')
    ->get();

Note that this approach leaves the WHERE clause sargable, meaning that an index on the created_at column can be used.

answered Jul 21 '19 at 03:00

Tim Biegeleisen

502,043
27
286
360

Your laravel code is the same thought as my laravel code. Nothing works – Rubics Jul 21 '19 at 03:08
Refer to this [link](https://stackoverflow.com/questions/16347649/sql-between-not-inclusive/16347680#16347680). – Rubics Jul 21 '19 at 03:08
My answer is certainly not the same as your code, and it should work assuming that `created_at` is a `datetime` column. – Tim Biegeleisen Jul 21 '19 at 03:14

score 1 · Answer 3 · answered Mar 16 '21 at 07:01

1

Easy Solution

$student = $this->student
->whereBetween(DB::raw('date(created_at)'), ['2019-07-21', '2019-07-21'])
->select('*')
->get()

answered Mar 16 '21 at 07:01

vaghela chirag

141
10

WhereBetween dates with cast in laravel

3 Answers3