douweng3564
2017-10-05 17:24
浏览 485
已采纳

SQL - DATE类型格式为YYYY-MM-DD,但UPDATE格式为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:

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

图片转代码服务由CSDN问答提供 功能建议

我已经设置了数据库并使用 DATE -Type设置了一个字段。 当我查看我的数据库时,格式为: YYYY-MM-DD 但我只能在插入此格式时更新此字段: YYYYMMDD with my statement。

为什么插入格式不是 DATE -Type格式?

我的PDO语句: \ n

  $ 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'  ] 
)); 
   
 
 

我的POST数据:

  mitarbeiternummer_abwesenheit:26 
abwesend_von:20171001 
abwesend_bis  :20171031 
   
 
 

一些信息:

  • 数据库:服务器版本:5.6.19-67.0-log
  • 数据库:MySQL-Client-Version:5.1.61
  • 编程语言为PHP
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douchangmian0305 2017-10-05 19:26
    已采纳

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

    已采纳该答案
    打赏 评论
  • dongzhong7299 2017-10-05 17:33

    The query should be something like:

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

    as an example.

    打赏 评论

相关推荐 更多相似问题