Incorrect datetime value: '2018' for column when passing valid date as string

Question

Today i experience a very strange issue. Appearently the date passed is not the same as the one bind_param passes to my query.

The database structure:

|id varchar(16) autoincrement|date_created datetime|date_updated datetime|

Currently the table contains valid data:

|1|2018-01-25 16:53:40|2018-01-25 16:53:40|

Now i am trying to execute a prepared UPDATE statement:

UPDATE `table1` SET `date_created` = ?, `date_updated` = ? WHERE `id` = ?

and i am binding following data:

["date_created"]=> string(19) "2018-01-25 16:53:40"
["date_updated"]=> string(19) "2018-01-25 17:02:57"
["id"]=> int(1) 1

but for any reason i get the error

1292: string(67) "Incorrect datetime value: '2018' for column 'date_created' at row 1"

I do not understand why bind_params or execute should cut down the valid date to just the year

EDIT: The PHP code, as requested. It is a function that automatically generates the query and executes it. I added a part that var_dumps the "should be executed" query

public function executeUpdateQuery(){
    // update existing record
    $where['id'] = 1;
    $array['date_created'] = self::getTimeStamp();
    $array['date_updated'] = self::getTimeStamp();
    if(!$this->updateRecord($where, $array)){
        print("<br>Error:<br>");
        var_dump($this->db->error);
    }
}

public static function getTimeStamp(){
    return date('Y-m-d H:i:s');
}

/**
 * @param array $array
 * @return string
 */
public static function getTypes(array $array = []) : string {
    $types = "";
    foreach ($array as $value){
        if(is_int($value))
            $types .= "i";
        elseif(is_string($value))
            $types .= "s";
        elseif(is_bool($value))
            $types .= "i";
        else
            $types .= "s";
    }
    return $types;
}

public function updateRecord(array $array = [], array $fields = []){

    if(empty($array) || empty($fields)) return FALSE;
    /** @var \mysqli_stmt $stmt */
    $query = "UPDATE `" . $this->tablename . "` SET `" . implode("` = ?, `", array_keys($fields)) . "` = ? WHERE `" . implode("` = ? AND `", array_keys($array)) . "` = ?;";
    $stmt = $this->db->prepare($query);

    $types = self::getTypes($fields);
    $types .= self::getTypes($array);
    if(!$stmt->bind_param($types, ...array_values($fields), ...array_values($array))) {
        return FALSE;
    }
    $result = $stmt->execute();
    $resultquery = $query;
    foreach ($fields as $field){
        $resultquery = $this->str_replace_first('?', (is_int($field)?$field:"'".$field."'"), $resultquery);
    }
    foreach ($array as $field){
        $resultquery = $this->str_replace_first('?', (is_int($field)?$field:"'".$field."'"), $resultquery);
    }
    var_dump($resultquery);
    print("<br>Fields:<br>");
    var_dump($fields);
    print("<br>Affected Rows:<br>");
    var_dump(mysqli_affected_rows($this->db));
    print("<br>Errors:<br>");
    var_dump(mysqli_errno($this->db));
    var_dump(mysqli_error($this->db));
    var_dump(mysqli_error_list($this->db));
    return $result;
}

public function str_replace_first($from, $to, $subject){
    $from = '/'.preg_quote($from, '/').'/';

    return preg_replace($from, $to, $subject, 1);
}

@RaymondNijland I will add it, but be greatful of confusion - because i auto-generate my queries — inxomnyaa, Jan 25 '18 at 17:17

score 0 · Answer 1 · answered Jan 25 '18 at 17:30

Appearently the issue was that the datetime field was bound as integer instead of a string, because i mixed up these 2 arrays:

$types = self::getTypes($fields);
$types .= self::getTypes($array);

Due to that, the string "2018-01-25 16:53:40" was casted into the integer 2018

The actually executed bind_param that was executed looked like:

bind_param(iss, a string, a string, an integer);

Incorrect datetime value: '2018' for column when passing valid date as string

1 Answers1