Doctrine 2: Can't update DateTime column on SQL Server 2008apm

datetime, doctrine, doctrine-orm, php, sql-server-2008

Solution

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

Problem

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?

Original source