Uploaded image for project: 'Doctrine DBAL'
  1. Doctrine DBAL
  2. DBAL-226

DATETIME2 in MSSQL - declared, but not supported

    Details

    • Type: Bug
    • Status: Closed
    • Priority: 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
        rivaros 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
        rivaros 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.
        Hide
        beberlei 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
        beberlei 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
        rivaros 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
        rivaros 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
        beberlei 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
        beberlei 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" );

          People

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

            Dates

            • Created:
              Updated:
              Resolved: