John Davidson

Message:

I have below query which is working if i run it directly on PHPMyadmin but not when i run it using laravel.

select SUM(p.quantity_held * s.current_share_price) as mv 

from portfolio_holding p, portfolios po, securities s 

where `p`.`security_id` = s.security_id and `po`.`portfolio_id` = p.portfolio_id 

      and `po`.`status_id` in (1, 2) 

      and (`p`.`sell_date` is null or `p`.`sell_date` = '0000-00-00' or `p`.`sell_date` > now()) 

      and s.security_id <> 0 

limit 1

It throws below error:

SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ''0000-00-00'

I have set mysql_mode to ALLOW_INVALID_DATES in database.

Also used below one when before executing my query in laravel:

DB::statement("set session sql_mode='ALLOW_INVALID_DATES'");

Also strict mode is set to false in laravel conig/database.php, but still above query throws error.

---

My Answer:
= '1970-01-01') _ and `p`.`quantity_held` > 0 _group by p.portfolio_id

To allow invalid dates in Laravel, you can modify the database configuration to allow zero dates. In your `config/database.php` file, add the following code to the MySQL connection configuration:

php
'options' => [
PDO::MYSQL_ATTR_ZERO_DATE_TO_NULL => true,
],


This will allow MySQL to treat zero dates ('0000-00-00') as NULL values, which should allow your query to work in Laravel as well.