Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-226

DATETIME2 in MSSQL - declared, but not supported

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.1.6
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      From Doctrine documentation

      27. Limitations and Known Issues
      ...
      27.2.2. Microsoft SQL Server and Doctrine "datetime"
      Doctrine assumes that you use DateTime2 data-types. If your legacy database contains DateTime datatypes then you have to add your own data-type (see Basic Mapping for an example).

      In reality, the type is not supported, failing with exception

      Could not convert database value "2012-02-15 01:25:12.0000000" to Doctrine Type datetime. Expected format: Y-m-d H:i:s.u

      The reason of such behavior is a bug in DBAL
      MsSQLPlatform::getDateTimeFormatString => 'Y-m-d H:i:s.u'

      u (milliseconds in PHP) occupy 6 digits, while datetime2 type has 7 digits.
      The solution is to truncate last digit before conversion

        Activity

        ross neacoders created issue -
        Hide
        Benjamin Eberlei added a comment -

        cCtually it is supported, when the DATETIME2 are created through Doctrine, because it makes DATETIME2(6) out of them to make DateTime#createFromFormat() support work. I am not sure how to fix this problem with "datetime" type, it will work when you use the VarDateTime types, these are a bit slower, but dont have this problem. Just call this in your bootstrap:

        \Doctrine\DBAL\Types\Type::overrideType("datetime", "Doctrine\DBAL\Types\Type\VarDateTime");
        
        Show
        Benjamin Eberlei added a comment - cCtually it is supported, when the DATETIME2 are created through Doctrine, because it makes DATETIME2(6) out of them to make DateTime#createFromFormat() support work. I am not sure how to fix this problem with "datetime" type, it will work when you use the VarDateTime types, these are a bit slower, but dont have this problem. Just call this in your bootstrap: \Doctrine\DBAL\Types\Type::overrideType( "datetime" , "Doctrine\DBAL\Types\Type\VarDateTime" );
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Hide
        ross neacoders added a comment -

        Beberlei,

        This issue is related to datetime2 MSSQL type (NOT datetime MSSQL type).

        Datetime2 is declared, but not supported - read above again.

        Show
        ross neacoders added a comment - Beberlei, This issue is related to datetime2 MSSQL type (NOT datetime MSSQL type). Datetime2 is declared, but not supported - read above again.
        ross neacoders made changes -
        Resolution Invalid [ 6 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Hide
        Benjamin Eberlei added a comment -

        I read your comment and did speak about DATETIME2.

        DATETIME in MsSQL only has 3 digits after the second. DATETIME2 by default has 7. However PHPs DateTime#createFromFormat() only supports 6 digits. That is why Doctrine creates MsSQL DATETIME2(6) columns, restricting to 6 digits. If your database already has DAteTime2 columns with 7 digits, then you have to register the VarDateTime type as detailed in my comment above.

        Show
        Benjamin Eberlei added a comment - I read your comment and did speak about DATETIME2. DATETIME in MsSQL only has 3 digits after the second. DATETIME2 by default has 7. However PHPs DateTime#createFromFormat() only supports 6 digits. That is why Doctrine creates MsSQL DATETIME2(6) columns, restricting to 6 digits. If your database already has DAteTime2 columns with 7 digits, then you have to register the VarDateTime type as detailed in my comment above.
        Benjamin Eberlei made changes -
        Status Reopened [ 4 ] Closed [ 6 ]
        Resolution Invalid [ 6 ]
        Hide
        ross neacoders added a comment -

        Understand, you mentioned "datetime" in your post, so I thought you misunderstood the issue.

        But, isn't checking if DATETIME2 is (7) and truncating last digit not a better option, if VarDateTime and date_create have bad speed?
        VarDateTime seems to work, but last 7th digit is not considered anyway.
        Also this will eliminate need to change something in bootstrap.

        Show
        ross neacoders added a comment - Understand, you mentioned "datetime" in your post, so I thought you misunderstood the issue. But, isn't checking if DATETIME2 is (7) and truncating last digit not a better option, if VarDateTime and date_create have bad speed? VarDateTime seems to work, but last 7th digit is not considered anyway. Also this will eliminate need to change something in bootstrap.
        Benjamin Eberlei made changes -
        Workflow jira [ 13464 ] jira-feedback2 [ 17849 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17849 ] jira-feedback3 [ 20204 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-226, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            ross neacoders
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: