Laravel compare database column string date with date with query builder DB

Laravel query

 

 

$billdate_raw = DB::raw("STR_TO_DATE(`bill_date`, '%d/%m/%Y')");

        $start_raw = DB::raw("STR_TO_DATE(?, '%d/%m/%Y')");

        $end_raw = DB::raw("STR_TO_DATE(?, '%d/%m/%Y')");

        $fromDate=$request->fromDate;

        $toDate=$request->toDate;

        $query = DB::table($id)

            ->select(

                "{$id}.*",

                'users.name as UserName',

                'users.emp_code as UserEmpCode',

                'users.email as UserEmail',

                'users.mobile as UserMobile',

                'users.mobile as UserMobile',

                'departments.department_name as UserDepartment',

                'claim_statuses.approval_one_status as ApprovedStatusOne',

                'claim_statuses.approval_one_code as ApproverCodeOne',

                'claim_statuses.approval_one_time as ApprovalTimeOne',

                'claim_statuses.approval_two_status as ApprovedStatusTwo',

                'claim_statuses.approval_two_code as ApproverCodeTwo',

                'claim_statuses.approval_two_time as ApprovalTimeTwo',

                'claim_statuses.approval_three_status as ApprovedStatusThree',

                'claim_statuses.approval_three_code as ApproverCodeThree',

                'claim_statuses.approval_three_time as ApprovalTimeThree'

            );

            if($request->fromDate !=null)

            {

                $query->whereBetween($billdate_raw, [$start_raw, $end_raw])

                ->setBindings(["$fromDate", "$toDate"]);

            }

         

            $query->join('users', 'users.user_id', '=', "{$id}.user_id")

            ->join('departments', 'departments.department_id', '=', 'users.department_id')

            ->join('claim_statuses', 'claim_statuses.table_id', '=', "{$id}.id")

            ->where('claim_statuses.table_name',$id);

           

           $data= $query->get();

 Plain sql query

$sqlQuery=DB::select('

           SELECT

                `internet_expense`.*,

                `users`.`name` AS `UserName`,

                `users`.`emp_code` AS `UserEmpCode`,

                `users`.`email` AS `UserEmail`,

                `users`.`mobile` AS `UserMobile`,

                `users`.`mobile` AS `UserMobile`,

                `departments`.`department_name` AS `UserDepartment`,

                `claim_statuses`.`approval_one_status` AS `ApprovedStatusOne`,

                `claim_statuses`.`approval_one_code` AS `ApproverCodeOne`,

                `claim_statuses`.`approval_one_time` AS `ApprovalTimeOne`,

                `claim_statuses`.`approval_two_status` AS `ApprovedStatusTwo`,

                `claim_statuses`.`approval_two_code` AS `ApproverCodeTwo`,

                `claim_statuses`.`approval_two_time` AS `ApprovalTimeTwo`,

                `claim_statuses`.`approval_three_status` AS `ApprovedStatusThree`,

                `claim_statuses`.`approval_three_code` AS `ApproverCodeThree`,

                `claim_statuses`.`approval_three_time` AS `ApprovalTimeThree`

            FROM

                `internet_expense`

            INNER JOIN `users` ON `users`.`user_id` = `internet_expense`.`user_id`

            INNER JOIN `departments` ON `departments`.`department_id` = `users`.`department_id`

            INNER JOIN `claim_statuses` ON `claim_statuses`.`table_id` = `internet_expense`.`id`

            WHERE

                STR_TO_DATE(`bill_date`, "%d/%m/%Y") BETWEEN STR_TO_DATE("01/01/2023", "%d/%m/%Y") AND STR_TO_DATE("03/01/2023", "%d/%m/%Y")

                AND `claim_statuses`.`table_name` = "internet_expense";');

           

            dd($sqlQuery);