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

        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" );
        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.
        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.
        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.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: