Skip to content

Loading…

DBAL-226: DATETIME2 in MSSQL - declared, but not supported #1401

Closed
doctrinebot opened this Issue · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user rivaros:

From Doctrine documentation

{quote}
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).
{quote}

In reality, the type is not supported, failing with exception
{quote}
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
{quote}

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

@doctrinebot

Comment created by @beberlei:

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");
@doctrinebot

Comment created by rivaros:

Beberlei,

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

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

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by rivaros:

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.

@doctrinebot doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.