Uploaded image for project: 'Doctrine DBAL'
  1. Doctrine DBAL
  2. DBAL-102

SQLSRV PDO doesn't support DateTime as a PHP Object

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1
    • Fix Version/s: 2.1.3
    • Component/s: Drivers
    • Labels:
      None
    • Environment:
      Windows 7, Microsoft SQL Server 2008, PHP SqlSrv Driver 2.0, PHP 5.3.6

      Description

      I have created an entity with has a datetime column

      Entity.php
          /**
           * @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.

        Activity

        aarondm Aaron DM created issue -
        Hide
        beberlei Benjamin Eberlei added a comment -

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

        Show
        beberlei Benjamin Eberlei added a comment - Are yo using columns of type "datetime" or "datetime2" in MSSQL? The former is not supported.
        Hide
        aarondm Aaron DM added a comment -

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

        Show
        aarondm Aaron DM added a comment - I did not know datetime was not supported. I am using the column type "datetime"
        Hide
        beberlei Benjamin Eberlei added a comment -

        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

        Show
        beberlei Benjamin Eberlei added a comment - 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
        Hide
        aarondm Aaron DM added a comment - - edited

        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)

        Show
        aarondm Aaron DM added a comment - - edited 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)
        Hide
        beberlei Benjamin Eberlei added a comment -

        Assigned to juokaz

        Show
        beberlei Benjamin Eberlei added a comment - Assigned to juokaz
        beberlei Benjamin Eberlei made changes -
        Field Original Value New Value
        Assignee Benjamin Eberlei [ beberlei ] Juozas Kaziukenas [ juokaz ]
        Hide
        aarondm Aaron DM added a comment - - edited

        When i ran profile, this is what I see

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

        Expliclitly converting the DateTiem to a string works

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

        Show
        aarondm Aaron DM added a comment - - edited When i ran profile, this is what I see " AND (a0_.deleted_at <= @P3) ORDER BY a0_.deleted_at DESC','810678','Object','Object'" Expliclitly converting the DateTiem to a string works $query->setParameter("deletedAt", $deletedAt->format("Y-m-d H:i:s.u"));
        Hide
        juokaz Juozas Kaziukenas added a comment -

        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.

        Show
        juokaz Juozas Kaziukenas added a comment - 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.
        Hide
        beberlei Benjamin Eberlei added a comment -

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

        Show
        beberlei Benjamin Eberlei added a comment - Does the error happen during a DQL or during flush (UPDATE/INSERT) ?
        Hide
        aarondm Aaron DM added a comment - - edited

        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.

        Show
        aarondm Aaron DM added a comment - - edited 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.
        Hide
        guilhermeblanco Guilherme Blanco added a comment -

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

        It seems it solved this issue.

        Show
        guilhermeblanco Guilherme Blanco added a comment - Is this issue still valid with new implementation of ParameterTypeInferer in ORM? It seems it solved this issue.
        Hide
        guilhermeblanco Guilherme Blanco added a comment -

        Closing since no response was given.

        Show
        guilhermeblanco Guilherme Blanco added a comment - Closing since no response was given.
        guilhermeblanco Guilherme Blanco made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Assignee Juozas Kaziukenas [ juokaz ] Guilherme Blanco [ guilhermeblanco ]
        Fix Version/s 2.1.3 [ 10162 ]
        Resolution Fixed [ 1 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira [ 12476 ] jira-feedback2 [ 17704 ]
        beberlei Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17704 ] jira-feedback3 [ 20059 ]
        Hide
        bunny1985 Michał Banaś added a comment -

        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.

        Show
        bunny1985 Michał Banaś added a comment - 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.
        Hide
        ocramius Marco Pivetta added a comment -

        Michał Banaś this actually works with the current implementation of the schema tools. Consider implementing additional formats a custom DBAL type instead.

        Show
        ocramius Marco Pivetta added a comment - Michał Banaś this actually works with the current implementation of the schema tools. Consider implementing additional formats a custom DBAL type instead.

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={expand=changesets[0:20].revisions[0:29],reviews, query=DBAL-102}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            guilhermeblanco Guilherme Blanco
            Reporter:
            aarondm Aaron DM
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: