Doctrine 2: Can't update DateTime column on SQ

Question

I'm using Doctrine 2.2 with php 5.3 on an apache server.

So far I've stumbled upon the following problem: When I try to update a datetime column I get: SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.

I've even gone so far to make a get onto the column and then use that with only 1 day added to it to set the new date......same result.

When I instead change both the column in the database and in the entity from datetime to date, it functions as intended.

My main problem is, that there are a few fields where I will NEED to use a datetime column.

Here's my code:

(birthdate was the column I changed to date....and is one of the few columns where that is possible for me):

//This returns the datetime object that represents birthdate from the database 
$help=$object->getBirthDate(); 
$help->setTimestamp(mktime($time[0],$time[1],$time[2],$date[2],$date[1],$date[0])); 
$help->format(\DateTime::ISO8601); 
$object->setBirthDate($help);

Does someone know a workaround here?

Answer 1

I ran into this problem with Doctrine 2.5 and SQL Server 2012. The problem is that the database field is type DATETIME, but doctirne only supports DATETIME2 on SQLServer2008Platform and up.

You should not be editing files in your vendor directory. The correct answer is to create a custom type: Doctrine Custom Mapping Types. In my case, I extended the current DateTimeType:

<?php

namespace AppBundle\Doctrine\Type;

use Doctrine\DBAL\Types\DateTimeType;
use Doctrine\DBAL\Platforms\AbstractPlatform;

class DateTime extends DateTimeType
{
    private $dateTimeFormatString = 'Y-m-d H:i:s.000';

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        return ($value !== null)
            ? $value->format($this->dateTimeFormatString) : null;
    }

}

And then in the Symfony config.yml:

    types:
      datetime: AppBundle\Doctrine\Type\DateTime

Answer 2

Its an official bug in Doctrine 2.2, to be resolved in 2.3.

Microseconds are being casted to string with wrong amount of zeros.

Workaround: Change function convertToDatabaseValue in File /Doctrine/DBAL/Types/DateTimeType.php:

public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
   if( $value === null)
      return null;

   $value = $value->format($platform->getDateTimeFormatString());

   if( strlen($value) == 26 &&
         $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u' &&
         ($platform instanceof \Doctrine\DBAL\Platforms\SQLServer2008Platform
          ||
         $platform instanceof \Doctrine\DBAL\Platforms\SQLServer2005Platform
         ) )
      $value = substr($value, 0, \strlen($value)-3);
   return $value;
}

Answer 3

I still had the same issue with Microsoft SQL Server Express 17 and Doctrine ORM v2.6.3/DBAL v2.9.2. (for Doctrine Platform, I use SQLServer2012Platform).

All you need to do is to change your SQL fields from DATETIME to DATETIME2 type (wherever possible)

Answer 4

What value are you passsing to the datetime field? You should pass a Datetime instance

$entity->setDate(new \Datetime());