[DBAL-860] MsSQL-Server DateTime microseconds issue Created: 11/Feb/11  Updated: 01/Apr/14  Resolved: 09/Jan/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.1
Fix Version/s: 2.2
Security Level: All

Type: Bug Priority: Major
Reporter: Martin Weise Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 0
Labels: None
Environment:

WindowsXP/Windows2008 R2 / PHP 5.3 / MsSQL-Server 2005 / MsSQL-PDO_PHP-Driver


Sub-Tasks:
Key
Summary
Type
Status
Assignee
DBAL-861 ensure the dateformat Y-m-d gets used... Sub-task Resolved Benjamin Eberlei  

 Description   

The string for the function getDateTimeFormatString() in the MsSqlPlatform class is 'wrong'.
The Microsoft-SQL-Server just uses 3 digits for microseconds and not 6.
So the string 'Y-m-d H:i:s.u' fails and the server states: [SQL Server]Fehler beim Konvertieren einer Zeichenfolge in einen datetime-Wert (Error when converting a string to a datetime-value) .

So this string works, but does not regard the microseconds for those how rely on them: 'Y-m-d H:i:s.000'

See also:
[...] The MS datetime column is documented to have an accuracy of only about .3 seconds anyway [...]
http://bytes.com/topic/sql-server/answers/80150-inserting-datetime-milliseconds-sql-server

http://msdn.microsoft.com/en-gb/library/ms186819.aspx (Section: Remarks)



 Comments   
Comment by Benjamin Eberlei [ 11/Feb/11 ]

Assigned to juozas, but i think the issue here is that you have to use Datetime2, or add your own type replacing the shipped one to support the old datetime.

Comment by Martin Weise [ 14/Feb/11 ]

Ok, I had another problem with the datetime, but this does not regard the problem of this issue ( at least not totally).
The problem with the MsSQL-Server before 2008 is that there is no data type named 'datetime2', just 'datetime'.
The next problem is that every date conversion for a query is done in the language set upon conection time.
Thus leads to a problem, when it is not possible to set the connection language.

So the problem is that the MsSQL-Server relies on the settings above.
In my case the datetime conversion failed, as the server always thought that the datetime-string would come in
the following format: Y-d-m . This is not true, as the default format string is: Y-m-d . So every insert/update query fails.
To solve the problem I did that: $entityManager->getConnection()->exec('SET DATEFORMAT ymd'); .
This way I ensured that the dateformat string works fine, except the issue problem.

To solve the problem in general, it would be helpful to subclass the MsSqlPlatform into a class named MsSql2005Platform or something like this and just override the getDateTimeFormatString and upon connection setting the format for the queries
as mentioned before.

Hope this helps out.
Besides, here a link to the datetime problem (in german): http://www.insidesql.org/blogs/frankkalis/2010/08/19/der-ultimative-guide-fuer-die-datetime-datentypen

Comment by Juozas Kaziukenas [ 14/Feb/11 ]

I have somehow manage to miss the fact that datetime2 wasn't around in datetime... What I'm thinking now is there a need for datetime2 in Doctrine at all. If only thing it brings is additional accuracy for microseconds, maybe best idea would be to use datetime for 2008 installs too if used from Doctrine. However datetime is now a standard and Microsoft recommends to use it for new installs. What I can do is I can always insert 3 fractional points to datetime column as both datetime2 and datetime would accept it as valid date string.

We can have separate platforms for 2008 and 2005 servers, but that would be quite resource intensive. Let me see what is the best way to fix it.

Regards to Dateformat, I guess the solution would be to set format on connection, how you suggested. How about you create a separate ticket for this and assign it to me.

Comment by Benjamin Eberlei [ 14/Feb/11 ]

Oracle also has an Session Init Listener that handles the date format things, i guess we can take this as example. However I think having Mssql2005Platform sounds goods also, it would be only one method to override.

Comment by Martin Weise [ 09/Aug/11 ]

To solve this issue, at least for MsSQL-Server datetime data types, change the following TypeClass of Doctrine by adding
this check before converting to PHP\DateTime in 'convertToPHPValue()' :

if( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u')
$value = $value.'000';

I know this is propably very specific, but I do not know, how other DBs handle microseconds in datetime strings.

Comment by Martin Weise [ 08/Sep/11 ]

I fixed this bug with some changes in the DateTimeType class. As there is no Explicit MSSQL2005 Plattform this change would also affect datetime2 type in the SQLServer 2008 plattform, which is the data type that has 6 microseconds.
So either populate a MSSQLServer2005 Plattform, or introduce a new DateTimeType for the 2005 platform.

DateTimeType.php

<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
 * <http://www.doctrine-project.org>.
 */

namespace Doctrine\DBAL\Types;

use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * Type that maps an SQL DATETIME/TIMESTAMP to a PHP DateTime object.
 *
 * @since 2.0
 */
class DateTimeType extends Type
{
    public function getName()
    {
        return Type::DATETIME;
    }

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getDateTimeTypeDeclarationSQL($fieldDeclaration);
    }

    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\MsSqlPlatform )
			$value = substr($value, 0, \strlen($value)-3);

		return $value;

    }

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

		if( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u')
			$value = $value.'000';

        $val = \DateTime::createFromFormat($platform->getDateTimeFormatString(),$value);
        if (!$val) {
            throw ConversionException::conversionFailedFormat($value, $this->getName(), $platform->getDateTimeFormatString());
        }
        return $val;
    }
}

Comment by Benjamin Eberlei [ 09/Jan/12 ]

Added SQLServer2005 platform that uses DATETIME and the .000 format as per instructions of Martin.

Generated at Sat Aug 30 00:16:58 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.