Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

usman31's avatar

Incorrect date value in whereDate

I'm using yajra datatables and I have a query which give this error "General error: 1525 Incorrect DATE value: ''

MY laravel query is like this "$business_id = request()->session()->get('user.business_id');

    $query = DB::table('overall_staff_service')->where('business_id',$business_id);

    if (!empty(request()->service_staff_id)) {
        $query->where('transaction_sell_lines.res_service_staff_id', request()->service_staff_id);
    }
  

    $location_id = request()->get('location_id', null);
    $permitted_locations = auth()->user()->permitted_locations();
  
    if (!empty($location_id) && $location_id != 'none') {       
        if ($permitted_locations == 'all' || in_array($location_id, $permitted_locations)) {              
                $query->where('location_id', '=', $location_id);              
        }
  
    } else {           
        if ($permitted_locations != 'all') {              
            $query->whereIn('location_id',$permitted_locations);
        } 
    }

    if (!empty(request()->start_date) && !empty(request()->end_date)) {
        $start = request()->start_date;
        $end =  request()->end_date;
        $query->whereDate('DATE', '>=', $start)
                ->whereDate('DATE', '<=', $end);
    }
            
    $query->select(
        'WHAT',
        'ST',
        'DATE',
        'SERVICE',
        'STAFF',
        'QTY',
        'INVOICE',
        'LOCATION',
        'BUSINESS',
        'CUSTOMER',
        'Customer_Mob',
        'STAFF_NOS',
        'business_id',
        'location_id',
        'INCENTIVE_RATE',
        'Tax%',
        'Tax',
        'Rate_w/o_Tax',
        'Rate_Inc_Tax',
        'Value_Inc_Tax',
        'Value_w/o_Tax',
        'Actual_Sale_Rate_inc_tax',
        'Actual_Sale_Value_inc_tax',
        'Actual_Sale_Rate_w/o_tax',
        'Actual_Sale_Value_w/o_tax',
        'Incentive%',       
		'Incen_Bill_Inc_Tax',
		'Incen_Bill_w/o_Tax',
		'Incen_Actual_Inc_Tax',
		'Incen_Actual_w/o_Tax',           
    );
  
    $datatable = Datatables::of($query)->make(true);"

when I do $query->toSql and run it with the bindings from the ajax in mysql(phpmyadmin) it runs fine and shows the result but in laravel it gives this error

0 likes
3 replies
tykus's avatar

What are the values of request()->start_date and request()->end_date in the Request? Maybe try parsing and formatting using Carbon (parse or createFromFormat method):

$start = Carbon::parse(request()->start_date)->format('Y-m-d');
$end =  Carbon::parse(request()->end_date)->format('Y-m-d');
usman31's avatar

@tykus I tried it also like parsing in carbon and doing createFromFormat but it doesn't work the values are '2022-05-01 as start', '2022-05-31' as end

usman31's avatar

I noticed that it works when I do where('DATE',$start) but when I do where('DATE','=',$start) it gives that error and it doesn't show any result with whereBetween also.

Please or to participate in this conversation.