1

I get empty result in eloquent query builder, whereas copying the sql (from ->toSql( ) function) and run it in phpadmin works perfectly. This is my query:

$reportData=DB::table('my_table')
->select('my_table.*')
->whereRaw("STR_TO_DATE(my_table.date, '%m.%Y') >= STR_TO_DATE('01.2017', '%m.%Y')")

The sql equivalent returned by toSql is:

select *
from my_table
where STR_TO_DATE(my_table.date, '%m.%Y') >= STR_TO_DATE('01.2017', '%m.%Y')

Note: I used another mysql function like UPPER, DATE_FORMAT in whereRaw, and that has no issue; but with STR_TO_DATE it has.

I checked in official site, STR_TO_DATE should work with MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.

Please help :)

--Update-- I rechecked mysql_log and here I had: - the first log (id=1) is created when running query through laravel - the second log (id=2) is from phpmyadmin

enter image description here

4givN
  • 2,151
  • 1
  • 17
  • 40
  • I removed the where clause, added a raw select with ```select('my_table.*', DB::raw("STR_TO_DATE(my_table.date, '%m.%Y') as foo")), no problem – 4givN Jan 16 '18 at 15:44
  • 2
    Different time zone settings influencing the date conversion maybe? Can you check whether `SHOW VARIABLES LIKE 'time_zone'` gives a different result when executed from Laravel, and directly in phpMyAdmin? – CBroe Jan 16 '18 at 15:54
  • Can you [enable the MySQL query log](https://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log) to see if there is a difference between the queries once they actually reach the server? – Moshe Katz Jan 16 '18 at 16:53
  • @CBroe : both laravel and phpmyadmin return "SYSTEM" as TIME_ZONE. I think, it's not in time_zone, I even changed the condition with ```STR_TO_DATE(my_table.date, '%m.%Y') >= '0'```, I have the same issue – 4givN Jan 17 '18 at 07:14
  • @MosheKatz : I updated my question with generated logs from Laravel & phpmyadmin. It seems both queries arrive to mysql. – 4givN Jan 17 '18 at 07:50

0 Answers0