Skip to content

Loading…

DBAL-102: SQLSRV PDO doesn't support DateTime as a PHP Object #949

Closed
doctrinebot opened this Issue · 14 comments

2 participants

@doctrinebot

Jira issue originally created by user aarondm:

I have created an entity with has a datetime column


    /****
     * @orm:Column(name="deleteAt", type="datetime")
     */
    protected $deletedAt;

    /****
     * Get DeletedAt
     * @return  \DateTime   
     */
    public function getDeletedAt() {
        return $this->deletedAt;
    }

    /****
     * Set DeletedAt
     * @param   \DateTime       $deletedAt
     */
    public function setDeletedAt(\DateTime $deletedAt) {
        $this->deletedAt = $deletedAt;
    }

This should work, however I get this error when trying to set a "deleteAt" date

$this->setDeletedAt(new \DateTime());
SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.

This should work afaik.

@doctrinebot

Comment created by @beberlei:

Are yo using columns of type "datetime" or "datetime2" in MSSQL? The former is not supported.

@doctrinebot

Comment created by aarondm:

I did not know datetime was not supported. I am using the column type "datetime"

@doctrinebot

Comment created by @beberlei:

In this case you have to add your own datetime type and change the timestamp format, or use the VarDateTime instead by calling "overrideType". Have a look at the Doctrine\DBAL\Types folder, escpecially the Type.php, DateTimeType.php and VarDateTimeType.php

@doctrinebot

Comment created by aarondm:

I have now tried this with the column type "datetime2", which creates the following date format in the database
"2011-03-27 06:07:00.000000"

And I am still getting the same error
"SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string."

P.S. this time I tried doing a select
WHERE deletedAt > (datetime)

@doctrinebot

Comment created by @beberlei:

Assigned to juokaz

@doctrinebot

Comment created by aarondm:

When i ran profile, this is what I see

" AND (a0.deleted_at <= @P3) ORDER BY a0_.deletedat DESC','810678','Object','Object'"

Expliclitly converting the DateTiem to a string works

$query->setParameter("deletedAt", $deletedAt->format("Y-m-d H:i:s.u"));

@doctrinebot

Comment created by juokaz:

Can you give a full code which you use to query this (or the smallest allowing to replicate). I can't see how DateTime object gets passed as a 'Object' to the query, this is handled by DBAL type system, not by mssql platform, which only specifies the date and/or time format.

@doctrinebot

Comment created by @beberlei:

Does the error happen during a DQL or during flush (UPDATE/INSERT) ?

@doctrinebot

Comment created by aarondm:

https://gist.github.com/6b2a8b53ece6e75abf20

The code there works (its not fully complete, but I tested the statements with the same Entity and etc and I am unable to successfully query the database).

Looks like it doesn't work even for a simple select. However if I do retrieve an entry, the getCreatedAt() does return a DateTime object correctly.

@doctrinebot

Comment created by @guilhermeblanco:

Is this issue still valid with new implementation of ParameterTypeInferer in ORM?

It seems it solved this issue.

@doctrinebot

Comment created by @guilhermeblanco:

Closing since no response was given.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by bunny1985:

Why is it close ? After one day of waiting for response?
Problem is not solved. It exist on ether datetime and datetime2 TSQL types.
And it is easy to check if you have sqlserver databes.
PLease repoen this issue.

@doctrinebot

Comment created by @ocramius:

[~bunny1985] this actually works with the current implementation of the schema tools. Consider implementing additional formats a custom DBAL type instead.

@doctrinebot doctrinebot added the Bug label
@doctrinebot doctrinebot added this to the 2.1.3 milestone
@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.