PHP MYSQL中的日期格式

Question

我有这两份声明。忽略安全风险( SECURITY )用户输入2016-7-9格式的日期。怎么才能得到和修改我试过的日期

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE time_paid BETWEEN ADDDATE('$time1',INTERVAL 0 HOUR) AND ADDDATE('$time2',INTERVAL '23:59' HOUR_MINUTE)";

如果$time1和$time2采用这种格式，则select语句无法工作。我可能错了，但我想它会以2016-01-12的形式出现吗？

这样做是可行的：

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE time_paid BETWEEN 2015-01-10 12:23:34 AND 2017-12-12 12:12:12";

有谁能在我的查询中使用这种格式: 2016-3-3？

Answer 1

将MySQL time_paid DATETIME列值格式化为与约束值匹配：

DATE_FORMAT(time_paid, '%Y-%c-%e') // 2016-3-3

哪里,

%Y - Four digits year e.g., 2000, 2001,…etc.

%c - Month in numeric e.g., 1, 2, 3…12

%e - Day of the month without leading zero e.g., 1,2,…31

因此，您的SQL应该是：

$sql =  "SELECT DISTINCT msisdn FROM customer WHERE DATE_FORMAT(time_paid, '%Y-%c-%e') BETWEEN ADDDATE('$time1',INTERVAL 0 HOUR) AND ADDDATE('$time2',INTERVAL '23:59' HOUR_MINUTE)";