Doctrine DBAL

DATETIME2 in MSSQL - declared, but not supported

Details

  • Type: Bug Bug
  • Status: Closed 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

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: