[DBAL-226] DATETIME2 in MSSQL - declared, but not supported Created: 20/Feb/12  Updated: 05/Mar/12  Resolved: 05/Mar/12

Status: Closed
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1.6
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: ross neacoders Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


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

Comment by Benjamin Eberlei [ 03/Mar/12 ]

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");
Comment by ross neacoders [ 05/Mar/12 ]


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

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

Comment by Benjamin Eberlei [ 05/Mar/12 ]

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.

Comment by ross neacoders [ 05/Mar/12 ]

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.

Generated at Wed Oct 07 15:47:31 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.