CODEWITHSUNDEEP
phpsql-serverpdotimestamp
digifrog
digifrog

Reputation: 77

"Syntax error or access violation" when inserting dates using PDO and SQL Server

I am trying to add two simple strings and one date to a SQL Server using PDO in PHP. I'm currently using the following code to do so:

$data = array(
    'Omschrijving' => 'Mijn mooie omschrijving...',
    'Toelichting' => 'Mijn leuke toelichting...'
);

# Insert data
$STH = $DBH->prepare("INSERT INTO memo (Datum, Omschrijving, Toelichting) VALUES (NOW(), :Omschrijving, :Toelichting)");
$STH->execute($data);

It works perfectly without the date, but for some reason it gives me the following error when I try to add the date:

SQLSTATE[42000]: Syntax error or access violation: 8180 [FreeTDS][SQL Server]Statement(s) could not be prepared. (SQLExecute[8180] at /builddir/build/BUILD/php-5.6.9/ext/pdo_odbc/odbc_stmt.c:254)

Does anyone know what I'm doing wrong?

Thanks in advance!

Upvotes: 1

Views: 1235

Answers (1)

chris85
chris85

Reputation: 23880

Now() is a MySQL function. GetDate() is the sql-server's equivalent. Here's their documentation on the function, https://msdn.microsoft.com/en-us/library/ms188383.aspx.

So provided code should become:

$data = array(
    'Omschrijving' => 'Mijn mooie omschrijving...',
    'Toelichting' => 'Mijn leuke toelichting...'
);

# Insert data
$STH = $DBH->prepare("INSERT INTO memo (Datum, Omschrijving, Toelichting) VALUES (GETDATE(), :Omschrijving, :Toelichting)");
$STH->execute($data);

Upvotes: 2

Related Questions