SQL - DATE类型格式为YYYY-MM-DD，但UPDATE格式为YYYYMMDD

Question

I have set up my database and set a field with the DATE-Type. When I look into my database the format is: YYYY-MM-DD but I can only update this field when I insert this format: YYYYMMDD with my statement.

Why is the insert format a other one than the DATE-Type format?

My PDO statement:

$stmt = $db->prepare("UPDATE mitarbeiter SET abwesend_von = :abwesend_von, abwesend_bis = :abwesend_bis WHERE id = :id");
      $stmt->execute(array(
        ':abwesend_von' => $_POST['abwesend_von'],
        ':abwesend_bis' => $_POST['abwesend_bis'],
        ':id' => $_POST['mitarbeiternummer_abwesenheit']
      ));

My POST Data:

mitarbeiternummer_abwesenheit:26
abwesend_von:20171001
abwesend_bis:20171031

Some informations:

• Database: Server Version: 5.6.19-67.0-log
• Database: MySQL-Client-Version: 5.1.61
• Programming language is PHP

Answer 1

Date and time values can be represented in several formats, such as quoted strings or as numbers, depending on the exact type of the value and other factors. For example, in contexts where MySQL expects a date, it interprets any of '2015-07-21', '20150721', and 20150721 as a date.

source

The parameterized mechanisms of the php library could be interpreting the parameter as something other than a string, resulting in the query being ... SET abwesend_von = 2017-01-05 ... which is equivalent to ... SET abwesend_von = 2011 ...; instead of ... SET abwesend_von = '2017-01-05' ...