Oracle filter with LIKE method always zero result

Hi, Im working with CI and oracle right now but i have difficulties if i use LIKE method.

My code like this :

Code:

$this->db->select('COUNT(*) AS FOUND');
        $this->db->where($where);
        $row = $this->db->get('"ci_r_user_log"')->row();

 And the result is zero for FOUND

I try to look the query and the result like :

Code:

SELECT COUNT(*) AS FOUND FROM "ci_r_user_log" WHERE "ul_datetime_login" LIKE '23-09-2019%'

I try run the query with SQL Developer and get 4 data found. I checked and it's correct.

So why the query that run with CI is not working? But the query is good

Im sorry if my english is bad.

That's not a problem with CI. It's more related to the way Oracle handle the dates columns. When you use a date as a string like you did in your where clause, Oracle will also need to convert the column values to a string to make the comparison. The format it will use depends on the server configuration or on your SQL client when you run the query manually. 

So the easy fix to always have the result you expect is to specify the format in your query. You can use TO_DATE to convert to a date object, or TO_CHAR to convert to a text string:

Code:

SELECT COUNT(*) AS FOUND FROM "ci_r_user_log" WHERE TO_CHAR(ul_datetime_login, 'DD-MM-YYYY') = '23-09-2019'

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/