Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3121

I resolved issue on date format with some exotics MSSQL server

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.x
    • Fix Version/s: None
    • Component/s: DQL, ORM
    • Security Level: All
    • Labels:

      Description

      A good way to resolve date issue on mssql is to use "SET DATEFORMAT" before each query. So I have done a quick and simple fix in our doctrine-dbal>Connection.php to get rid of the issue:

          private function patch_date( &$query ) {
              $driver = print_r($this->getDriver(), true);
              //SI EN MSSQL
              if ( stripos($driver, 'Sqlsrv') > 0 ) {
                  $query = 'SET DATEFORMAT ymd;'.$query;
              }
          }
      
          public function prepare( $statement ) {
              $this->connect();
              $this->patch_date($statement);
              return new Statement($statement, $this);
          }
      
          public function executeUpdate( $query, array $params = array(), array $types = array() ) {
              $this->connect();
              $this->patch_date($query);
      

      Simple and no need to manage it anymore.
      As I work on several MSSQL date formated server: it's very usefull.

        Activity

        Show
        Marco Pivetta added a comment - This should be implemented as a listener like https://github.com/doctrine/dbal/blob/9d6300fd862478f58a526fbcad0a8f630daa60aa/lib/Doctrine/DBAL/Event/Listeners/OracleSessionInit.php
        Hide
        Steve Müller added a comment -

        What exact date issue are you refering to here?

        Show
        Steve Müller added a comment - What exact date issue are you refering to here?
        Hide
        Gasc added a comment - - edited

        Thank you for the listener.
        We have usually no issue on MySQL and MSSQL 2008R2, but date setting were different on one 2008R2 of our customer server.
        Here is the error message we got:

        SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.

        Just to know: We use Symfony for the project (with doctrine "2.1.0"), and without time to rewrite services, so this tiny little patch saved us full of time.

        Show
        Gasc added a comment - - edited Thank you for the listener. We have usually no issue on MySQL and MSSQL 2008R2, but date setting were different on one 2008R2 of our customer server. Here is the error message we got: SQLSTATE [22007] : [Microsoft] [SQL Server Native Client 10.0] [SQL Server] La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites. Just to know: We use Symfony for the project (with doctrine "2.1.0"), and without time to rewrite services, so this tiny little patch saved us full of time.
        Hide
        Marco Pivetta added a comment -

        Yep, this is a non-issue for us, as it is a custom environment, as it seems.

        That should be solved with a post-connect listener as I've linked above.

        Closing as invalid.

        Show
        Marco Pivetta added a comment - Yep, this is a non-issue for us, as it is a custom environment, as it seems. That should be solved with a post-connect listener as I've linked above. Closing as invalid.

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Gasc
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: