CODEWITHSUNDEEP
sqllaravel
Rubics
Rubics

Reputation: 65

WhereBetween dates with cast in laravel

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?

Upvotes: 1

Views: 3409

Answers (3)

Chirag Vaghela
Chirag Vaghela

Reputation: 141

Easy Solution

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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.

Upvotes: 1

Pablo
Pablo

Reputation: 6048

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()

Upvotes: 2

Related Questions