ORA-01861: literal does not match format string

[ccavusoglu0]

Date Format problem while inserting Oracle through Laravel

Hi, I'm trying to insert datetime rows to Oracle by using Eloquent as I didn't want to use raw query and I'm getting ORA-01861: literal does not match format string error. NLS format we are using in Oracle is dd.MM.yyyy hh:mm:ss Normally when I insert directly from database I use this format to_date('10/11/2021 10:00:00', 'dd/mm/yyyy hh24:mi:ss') On Laravel I'm using carbon to format while passing dates as in Controller section below. In the Error Code section if the generated insert query is that, I thought maybe Oracle dont accept it since it doesn't have to_date function in the query. Any idea?

Controller:

        $attributes['x_id'] = intval(request()->x_id) ;
        $attributes['to_y_id'] = intval(request()->to_y_id) ;
        $attributes['start_date'] = Carbon::now()->format('d.m.Y H:i:s');
        $attributes['end_date'] = Carbon::now()->format('d.m.Y');
        $attributes['navi_date'] = Carbon::now()->format('d.m.Y H:i:s');
        $attributes['navi_user'] = 'USERNAME';
        ModelName::create($attributes);

Model Class:

class ModelName extends Model
{
    protected $guarded=[];
    protected $connection = 'oracle';
    protected $table = 'table_name';
    public $timestamps = false;

    protected $primaryKey='x_id' ;
    public $incrementing = false;
}

Error Code:

"""
Error Code    : 1861
Error Message : ORA-01861: literal does not match format string
Position      : 140
Statement     : insert into "SCHEMA_NAME"."TABLE_NAME" ("X_ID", "TO_Y_ID", "START_DATE", "END_DATE", "NAVI_DATE", "NAVI_USER") 
 values (:p0, :p1, :p2, :p3, :p4, :p5) ◀
Bindings      : [-101266,-101304,27.07.2022 16:02:24,27.07.2022,27.07.2022 16:02:24,USER]
 (SQL: insert into "SCHEMA_NAME"."TABLE_NAME" ("X_ID", "TO_Y_ID", "START_DATE", "END_DATE", "NAVI_DATE", "NAVI_USER") 
 values (-101, -102, 27.07.2022 16:02:24, 27.07.2022, 27.07.2022 16:02:24, USERNAME)) ◀
"""

Table Structure on Oracle

X_ID      NUMBER 
TO_Y_ID   NUMBER
START_DATE   DATE 
END_DATE     DATE 
NAVI_USER VARCHAR2(250) 
NAVI_DATE   DATE  DEFAULT SYSDATE

System details

• Operating System: Windows 10
• PHP Version: 7.3.31
• Laravel Version: 6.0.4
• Laravel-OCI8 Version: 6.0

[yajra]

Just bind carbon instance, no need to format:

$attributes['start_date'] = Carbon::now();
$attributes['end_date'] = Carbon::now();
$attributes['navi_date'] = Carbon::now();

[ccavusoglu0]

Oh I was not expecting this to work, since format in Oracle side in 2nd screenshot.

[sciracioglu]

you should change some lines in Oci8ServiceProvider file like this. $sessionVars = [ 'NLS_TIME_FORMAT' => 'HH24:MI:SS', 'NLS_DATE_FORMAT' => 'DD/MM/YYYY', 'NLS_TIMESTAMP_FORMAT' => 'DD/MM/YYYY HH24:MI:SS', 'NLS_TIMESTAMP_TZ_FORMAT' => 'DD/MM/YYYY HH24:MI:SS TZH:TZM', 'NLS_NUMERIC_CHARACTERS' => '.,', ];

[yajra]

The NLS date format was updated by the package to match the PHP default. If you want something different, you can also use DB::setDateFormat('DD/MM/YYYY HH24:MI:SS'). However, I do not recommend this as it will cause some breaking behavior when dealing with dates. Use it as needed but not as default.