Cannot INSERT date with prepared statement

Question

I am trying to do a multi-row INSERT for each day of a given month/year.

If the loop runs just once, everything works as expected. If it runs 2 or more times, it throws the below error:

SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with date (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:260)

It also works if I don't prepare the date. I'm at a loss... am I doing something wrong? This is my code:

$query = "INSERT INTO scp.schedule
    (record_create_date, associate_id, scheduled_date, shift_id, is_weekend)
    VALUES ";

for ($day_count = 1; $day_count <= $days_month; $day_count++)
{
    $day_count = sprintf("%02d", $day_count);

    $full_date = $year . "-" . $month . "-" . $day_count;

    // Check if date is weekend
    $weekend = (date("N", strtotime($full_date)) < 6) ? 0 : 1;

    $query .= "(GETDATE(), ?, ?, ?, ?), ";
    $params[] = $associate["associate_id"];
    $params[] = $full_date;
    $params[] = $shifts[0]["shift_id"];
    $params[] = $weekend;
}

// Remove the last comma and space
$query = substr($query, 0, -2);

$stmt = $db->prepare($query);
$stmt->execute($params);

I am using SQL server version 12+ and PHP version 7+. The column type in the DB is DATE.

EDIT:

This works

INSERT INTO scp.schedule 
    (record_create_date, associate_id, 
     scheduled_date, shift_id, is_weekend) 
VALUES (GETDATE(), ?, ?, ?, ?)

This does not

INSERT INTO scp.schedule 
    (record_create_date, associate_id, 
     scheduled_date, shift_id, is_weekend) 
VALUES (GETDATE(), ?, ?, ?, ?), 
       (GETDATE(), ?, ?, ?, ?)

EDIT 2:

Some tests and the errors they throw. I've also tried using '?'.

$query .= "(GETDATE(), ?, DATEFROMPARTS(?, ?, ?), ?, ?), ";
// Also tried intval() and casting to int
$params[] = $year;
$params[] = $month;
$params[] = $day_count;

SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:260)

$query .= "(GETDATE(), ?, CONVERT(date, ?), ?, ?), ";

SQLSTATE[22018]: Invalid character value for cast specification: 529 [Microsoft][ODBC SQL Server Driver][SQL Server]Explicit conversion from data type text to date is not allowed. (SQLExecute[529] at ext\pdo_odbc\odbc_stmt.c:260)

For those of us that cannot be bothered to work out what the query would look like, can you add the result of an echo $query; to your question so we can see what you are actually trying to run as the query — RiggsFolly
What does $full_date look like? Remember DATE columns are not DATETIME columns. — delboy1978uk
You don't have enough values in $params to satisfy all the ? in the query — Nick

Zhorov Zhorov · Accepted Answer · 2018-09-27T11:50:39

You may try to avoid passing strings as values for date field in SQL Server using DATEFROMPARTS() or CONVERT() T-SQL functions.

<?
$query = "INSERT INTO scp.schedule
    (record_create_date, associate_id, scheduled_date, shift_id, is_weekend)
    VALUES ";

for ($day_count = 1; $day_count <= $days_month; $day_count++) {
    $day       = $day_count;
    $day_count = sprintf("%02d", $day_count);
    $full_date = $year . "-" . $month . "-" . $day_count;

    // Check if date is weekend
    $weekend = (date("N", strtotime($full_date)) < 6) ? 0 : 1;

    # Using DATETIMEFROMPARTS
    $query .= "(GETDATE(), ?, DATEFROMPARTS(?, ?, ?), ?, ?), ";
    $params[] = $associate["associate_id"];
    $params[] = $year;
    $params[] = $month;
    $params[] = $day;
    $params[] = $shifts[0]["shift_id"];
    $params[] = $weekend;

    # Using CONVERT
    /*
    $query .= "(GETDATE(), ?, CONVERT(date, ?), ?, ?), ";
    $params[] = $associate["associate_id"];
    $params[] = $full_date;
    $params[] = $shifts[0]["shift_id"];
    $params[] = $weekend;
    */
}

// Remove the last comma and space
$query = substr($query, 0, -2);
$stmt = $db->prepare($query);
$stmt->execute($params);

?>

If it is not necessery to insert all days in one statement, you may consider to prepare your statement once and execute it in every iteration. Then, if error occures, you will know what values are causing problem.

Cannot INSERT date with prepared statement

2 Answers