0

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.

nacho
  • 5,280
  • 2
  • 25
  • 34
DS9
  • 2,995
  • 4
  • 52
  • 102
  • NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes must be not set. Strict mode must be disabled too. PS. But I'd recommend to replace invalid dates values with NULLs. – Akina Jun 09 '21 at 07:29
  • `DB::statement("set session sql_mode='ALLOW_INVALID_DATES'");` This cannot help - zero is not valid month or day value. Read this mode description carefully. – Akina Jun 09 '21 at 07:32
  • i only have ALLOW_INVALID_DATES sqm_mode in database other then that doesn't have naything on database. – DS9 Jun 09 '21 at 07:33
  • I'll repeat - ALLOW_INVALID_DATES claims `'0000-00-00'` value **incorrect**!!! One more time - read this mode description **CAREFULLY**!!! – Akina Jun 09 '21 at 07:34
  • aah okay then which mode i should use to solve this one. – DS9 Jun 09 '21 at 07:37
  • the issue is its working with PHPMyadmin but not with laravel. So what changes i should do other then setting dates with NULL value. – DS9 Jun 09 '21 at 07:50
  • Check sf strict mode is enabled in `config/database.php` in the configuration you're using. Disable it. – IGP Jun 09 '21 at 17:14
  • @IGP it is already set as false – DS9 Jun 10 '21 at 09:04

0 Answers0