Symfony – Doctrine type datetime format errors with Oracle

When using an Oracle Database in combination with the Symfony Framework and Doctrine ORM, you will probably encounter this error.

Problem

Could not convert database value "01-JAN-22 01.00.00 PM" to Doctrine Type datetime. 
Expected format: Y-m-d H:i:s

I have found this problem to be existing also in the latest 4.4 version when using doctrine 2.
Later I will try to replicate this error within Symfony 5 or 6 and post my findings here.

Solution

Database Server-Side

The long-term solution is to change the date format in the oracle configuration directly.

NLS*TIME*FORMAT="HH24:MI:SS"
NLS*DATE*FORMAT="YYYY-MM-DD"
NLS*TIMESTAMP*FORMAT="YYYY-MM-DD HH24:MI:SS"
NLS*TIMESTAMP_TZ*FORMAT="YYYY-MM-DD HH24:MI:SS TZH:TZM"

Just be careful when changing the global configuration because it affects ALL datetime formats, so it may break applications using existing datetime fields. If the database is new and not yet in production then you should be fine, otherwise you should consider using the following way.

Some more detailed explanation to this topic can be found here.

Application Client-Side

You can add the following configuration to your services.yaml to solve the problem.

services:
  # Arbitrary name for the listener
  oracle.listener:
    class: Doctrine\DBAL\Event\Listeners\OracleSessionInit
    arguments:
      - { NLS_TIMESTAMP_TZ_FORMAT: "YYYY-MM-DD HH24:MI:SSTZH:TZM" }
    tags:
      - { name: doctrine.event_listener, event: postConnect }

The downside to this solution is that you have to configure this in all doctrine applications that use the Oracle database.

Explanation

The problem occurs because Oracle has its own format here which differs to other database management systems.
Here you can find a comment from Ocramius explaining this.

Oracle stores datetime in a different format than other database management systems do.