How to bind ISO8601 TSQL DATETIME parameter with PDO?

Question

It seems that PDO has a problem with ISO 8601 formatted timestamps.

I'm connecting from 64-bit Ubuntu 16.04 running PHP 7.0.8 using the Microsoft® ODBC Driver 13 (Preview) for SQL Server®

Here's my simple table:

CREATE TABLE dtest (
    "stamp" DATETIME
);

Works:

$pdoDB = new PDO('odbc:Driver=ODBC Driver 13 for SQL Server;
  Server='.DATABASE_SERVER.';
  Database='.DATABASE_NAME,
  DATABASE_USERNAME,
  DATABASE_PASSWORD
);
$pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "INSERT INTO dtest (stamp) VALUES ('2011-03-15T10:23:01')";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);

Does not work:

$sql = "INSERT INTO dtest (stamp) VALUES (?)";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15T10:23:01'];
$stmt->execute($params);

Fatal error: Uncaught PDOException: SQLSTATE[22018]: Invalid character value for cast specification: 0 [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification (SQLExecute[0] at /build/php7.0-lPMnpS/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)

This works if I delete the T so '2011-03-15T10:23:01' becomes '2011-03-15 10:23:01'

$sql = "INSERT INTO dtest (stamp) VALUES (?)";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15 10:23:01'];
$stmt->execute($params);

But I'm writing a script that runs nightly on about 2 million records, so I'd really rather not bear the overhead of running millions of str_replace('T', ' ', $param)

I've also tried using bindParam, but it gives the same error:

$sql  = "INSERT INTO dtest (stamp) VALUES (:tdate)";
$stmt = $pdoDB->prepare($sql);
$date = '2011-03-15T10:23:01';
$stmt->bindParam(':tdate',$date,PDO::PARAM_STR);
$stmt->execute();

Is there anyway to bind and execute this parameter as is? I'm a little dubious of the error message because it appears to be coming from SQL Server as if PDO did its job fine, but that doesn't make sense since it's able to handle the type conversion without parameterization.

I've also tried SQL conversion:

Works:

$sql = "INSERT INTO dtest (stamp) VALUES (CONVERT(DATETIME, '2011-03-15T10:23:02', 126))";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);

Does not Work:

$sql = "INSERT INTO dtest (stamp) VALUES (CONVERT(DATETIME, ?, 126))";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15T10:23:02'];
$stmt->execute($params);

@MonkeyZeus nope, but I've been off all weekend so I'm gonna take another stab at it today. — Jeff Puckett, Aug 22 '16 at 13:47
@JeffPuckettII Could you solve this? This doesn't happen only with dates, but all bound parameters. — Frondor, Feb 25 '17 at 15:49
@Frondor for this particular problem, I was importing safe (pre-sanitized) data, so I skipped the binding altogether. Not a solution for user-input, so no, I still haven't solved it. I just try to avoid Microsoft's poorly supported junk as much as possible. — Jeff Puckett, Feb 25 '17 at 19:49

score 1 · Answer 1 · edited Aug 19 '16 at 21:35

1

You will need to use SQL Server's built-in convert() function and specify the format (126) which you are giving it:

$sql = "INSERT INTO dtest (stamp) VALUES (CONVERT(DATETIME, '2011-03-15T10:23:01', 126))";

The documentation mentions :mmm at the end of your string so you might need to manually add :000 at the end of your date string for this to work.

edited Aug 19 '16 at 21:35

Jeff Puckett

37,464
17
118
167

answered Aug 19 '16 at 18:30

MonkeyZeus

20,375
4
36
77

Thanks for your answer, but I get the same error when trying `$sql = "INSERT INTO dtest (stamp) VALUES (convert(?, 126))";` Also note that the unprepared statement works just fine without conversion. `$sql = "INSERT INTO dtest (stamp) VALUES ('2011-03-15T10:23:01')";` It's the binding process that causes an issue. – Jeff Puckett Aug 19 '16 at 18:37
In fact, this causes an additional `Syntax error or access violation: 102 Incorrect syntax near '@P1'.` – Jeff Puckett Aug 19 '16 at 18:41
@JeffPuckettII Whoops! Sorry about that, I should have read your question details more carefully. That's weird though. Have you considered using [`bindParam()`](http://php.net/manual/en/pdostatement.bindparam.php) with `PARAM_STR` instead and seeing if things get any better? – MonkeyZeus Aug 19 '16 at 18:48
Nope, that gives the same error - I have updated my question with the attempt. – Jeff Puckett Aug 19 '16 at 19:07
1

@JeffPuckettII I'm all out of ideas, it's possible that the mssql driver simply has an unresolved bug :( – MonkeyZeus Aug 19 '16 at 19:21
@JeffPuckettII You mention that this code is used in a nightly script. Is there any reason, besides feeling good about parameterization :-), to not trust the data being inserted and just ignore binding for that field? – MonkeyZeus Aug 19 '16 at 19:37
You're right - I think that should be OK for this field, but I'm under policy restrictions and that won't pass code review. If performance limitations persist though, then I might pursue an exemption. – Jeff Puckett Aug 19 '16 at 19:49
CONVERT requires a data type, I.e. convert(datetime, '2011-03-15T10:23:01', 126) – Laughing Vergil Aug 19 '16 at 20:07
@LaughingVergil Oh WOW, that is silly of me. Thanks! – MonkeyZeus Aug 19 '16 at 20:27
@JeffPuckettII Check out Laughing Vergil's comment above. I don't have time to update my answer so give it a shot! – MonkeyZeus Aug 19 '16 at 20:28
@LaughingVergil thanks for the sytnax fix, but that still results in the original error. I have updated my question with the attempt. – Jeff Puckett Aug 19 '16 at 21:38

score 1 · Answer 2 · answered May 31 '17 at 09:23

1

After half a day spent trying to resolve the same issue, I ended up dropping odbc and using dblib instead. I installed php7.0-sybase package, adapted the data source name of my PDO connection and resolved once for all. Now every bind is working.

answered May 31 '17 at 09:23

Massimiliano Arione

2,422
19
40

Technically speaking, this is not the answer to the specific question, but it is such a damn good workaround alternative that I'm extremely tempted to "accept" it. You're right, I've been loving the Sybase drivers so much that I haven't used ODBC in ages. But I know I'll have more projects in the future (e.g. DB2) that will need ODBC and hopefully someone can answer that particularly. – Jeff Puckett Sep 26 '17 at 02:41

How to bind ISO8601 TSQL DATETIME parameter with PDO?

Works:

Does not work:

Works:

Does not Work:

2 Answers2

Linked