PDO中的日期匹配错误 [英] Error matching dates in PDO

问题描述

我正在尝试更新日期列与当前年份和月份相匹配的数据库表上的记录.我不断收到此错误

I am trying to update records on a database table where the date column matches the present year and month. I keep getting this error

日期时间值错误:列"created_at"为％2018-06％"

Incorrect datetime value: '%2018-06%' for column 'created_at'

这就是我在做的

$current_month = date("Y-m");
$points = 4;
$freelancer_id =4;
$update = "UPDATE monthly_limits SET points = :points WHERE freelancer_id = :freelancer_id AND 
created_at LIKE concat('%', :current_month, '%')";
$query = $this->db->prepare($update);
$query->bindParam("points", $points);
$query->bindParam("current_month", $current_month);
$query->bindParam("freelancer_id", $freelancer_id);
$query->execute();

请问该如何解决?

推荐答案

这不是SQL数据库中日期的工作方式. like语句应与字符串类型一起使用，而日期则是美化的数字.给定参数的样式，我敢打赌，最好的选择是进行两次差异检查.每月一次，一年一次.

This is not how dates work in a SQL database. A like statement is meant to be used with string types, whereas dates are a glorified number. Given the style of parameters, I'm betting your best bet is to do two difference checks. One on month and one on year.

您尚未指定RDBMS，因此我将给出一个MySQL答案.

You haven't specified your RDBMS, so I'll give a MySQL answer.

UPDATE 
  monthly_limits SET points = :points 
WHERE 
  freelancer_id         = :freelancer_id 
  AND Year(created_at)  = :current_year
  AND Month(created_at) = :current_month

这篇关于PDO中的日期匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！