Error Message : ORA-01841 when trying to filter records by date-CodePudding

I am using Oracle Database with Laravel application I have Record model and its database table is items_view which is a Database View, now I want to get all records with 'INVH_DT' column (which is of date type) equals to a specific date so I am using this:

$records = Record::where('INVH_DT', 'to_date(\'22/06/2022\' , \'DD/MM/YYYY\')' )->get();

but I have this error:

Error Code : 1841 Error Message : ORA-01841: (full) year must be between -4713 and 9999, >and not be 0 Position : 46 Statement : select * from "ITEMS_VIEW" where "INVH_DT" = :p0 >Bindings : [to_date('22/06/2022' , 'DD/MM/YYYY')] select * from "ITEMS_VIEW" where "INVH_DT" = to_date('22/06/2022', 'DD/MM/YYYY')

I tried this statement elect * from "ITEMS_VIEW" where "INVH_DT" = to_date('22/06/2022' , 'DD/MM/YYYY') on Oracle SQL Developer and it works fine. I tried other date formats like 'YYYYMMDD' and it works fine on SQL Developer while giving the same error of Laravel.

CodePudding user response：

I don't know Laravel, but the resulting SQL command looks like this:

select * from "ITEMS_VIEW" where "INVH_DT" = :p0

where the :p0 parameter is replaced with to_date('22/06/2022' , 'DD/MM/YYYY'). It will not work that way.

Maybe try this:

$records = Record::where("to_char(INVH_DT, 'DD/MM/YYYY')", '22/06/2022')->get();

CodePudding user response：

I figured out that Laravel deals with this issue by Query builder's whereDate clause Additional Where Clauses and this just worked as expected:

$records = Record::whereDate('INVH_DT', '2022-06-22')->get();