CODEWITHSUNDEEP
phpmysqldate
Martin
Martin

Reputation: 83

SQL - DATE Type format is YYYY-MM-DD, but UPDATE is format YYYYMMDD

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:

Upvotes: 1

Views: 252

Answers (2)

Uueerdo
Uueerdo

Reputation: 15951

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' ...

Upvotes: 2

farbiondriven
farbiondriven

Reputation: 2468

The query should be something like:

UPDATE `foo_schema`.`foo` SET `foo_date`='2017-04-03' WHERE `id`='1';

as an example.

Upvotes: 1

Related Questions