Bug #74626 [Opn->Nab]: PDO ODbC does not paramaterize datetimeoffset in Microsoft SqlServer right

Edit report at https://bugs.php.net/bug.php?id=74626&edit=1 ID: 74626 Updated by: cmb@php.net Reported by: zippy1981 at gmail dot com Summary: PDO ODbC does not paramaterize datetimeoffset in Microsoft SqlServer right -Status: Open +Status: Not a bug Type: Bug Package: PDO ODBC Operating System: Windows 10 PHP Version: 7.1.5 -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: Indeed, at least the ODBC Driver for SQL Server and the SQL Server Native Client do not accept the 'T' in the date/time value[1]. date("Y-m-d H:i:sP") should work fine. I don't think we should work around that for PDO_ODBC; if it works with pdo_sqlsrv, fine – that extension appears to be better for interfacing with SQL Server anyway. [1] <https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements#data-formats-strings-and-literals> Previous Comments: ------------------------------------------------------------------------ [2017-05-22 02:33:28] zippy1981 at gmail dot com Description: ------------ Lets say I have the following temp table defined: DROP TABLE IF EXISTS #dateTable; CREATE TABLE #dateTable ( Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1), [timestamp] DATETIMEOFFSET NOT NULL, message NVARCHAR(255) NOT NULL ); And I have an odbc pdo connection $this->cn. I can insert a row with $sql = <<< EOSQL INSERT INTO #dateTable (message, [timestamp]) VALUES ( 'Solid string insert.', '%s' ); EOSQL; $this->cn->exec(sprintf($sql); However, if I paramaterize it like so: $sql = <<< EOSQL INSERT INTO #dateTable (message, [timestamp]) VALUES ( 'Solid string insert.', :timestamp ); EOSQL; $stmt = $this->cn->prepare($sql); None of the following options work: 1. $stmt->execute([date(DATE_ATOM)]); 2. $timestamp = date(DATE_ATOM); $stmt->bindParam(':timestamp', $timestamp); $result = $stmt->execute(); 3. $stmt->bindValue(':timestamp', date(DATE_ATOM)); $result = $stmt->execute(); However, if I do PDO with the SqlSvr driver it works just fine. Test script: --------------- https://github.com/zippy1981/PhpSqlServerDateTime Actual result: -------------- PDOException: SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Operand type clash: text is incompatible with datetimeoffset (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:260) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=74626&edit=1