How to query the Date part only on laravel whereBetween()

Question

Good day,

Im confuse on how can I perform a query on laravel's whereBetween function in evaluating only the Date part and disregarding the time part.

$startDate = '2015-04-31';
$endDate = '2015-05-01';
$totalDebit = DB::table('x_general_transactions_details')
            ->whereBetween('date_at', array($startDate,$endDate))
            ->where('account_xid',$account->xid)
            ->sum('debit');

the problem here is that all the transaction in '2015-05-01' is not being included because its time is not 00:00:00 that's why in order to get the transactions on '2015-05-01' i have to add one day to my $endDate variable.

Mohammad · Accepted Answer · 2015-05-22 13:00:05Z

1

try this :

$startDate = Carbon::createFromFormat('Y-m-d','2015-04-31');
$endDate = Carbon::createFromFormat('Y-m-d','2015-05-1');
$totalDebit = DB::table('x_general_transactions_details')
            ->whereBetween('date_at', array($startDate,$endDate))
            ->where('account_xid',$account->xid)
            ->sum('debit');

this code calculate all transactions from 2015-04-31 00:00:01 Am to 2015-05-01 00:00:01 am if you want include all transaction on 2015-05-01 included use this code for end time

$endDate = Carbon::createFromFormat('Y-m-d H:i:s','2015-05-1' . '23:59:59');

answered May 22, 2015 at 13:00

Mohammad

6761 gold badge7 silver badges19 bronze badges

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

1 Comment

melvnberd Over a year ago

Thank you for your answer @user1873569.. I guess this is the easiest way to solve the problem :)

buzdyk · Accepted Answer · 2015-05-22 12:55:20Z

1

Add a time to your dates, it will save you a day.

$startDate = '2015-04-31 00:00:00';
$endDate   = '2015-05-01 23:59:59';

answered May 22, 2015 at 12:55

buzdyk

6226 silver badges14 bronze badges

Comments

lukasgeiter · Accepted Answer · 2015-05-22 13:09:14Z

1

I personally would just cast the database field to a date:

$totalDebit = DB::table('x_general_transactions_details')

        ->whereBetween(DB::raw('DATE(date_at)'), array($startDate,$endDate))
                       ^^^^^^^^^^^^^^^^^^^^^^^^

        ->where('account_xid',$account->xid)
        ->sum('debit');

answered May 22, 2015 at 13:09

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

Collectives™ on Stack Overflow

How to query the Date part only on laravel whereBetween()

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related