php 如何检查字符串是否是有效的日期、时间或日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2690205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a string is a valid DATE, TIME or DATETIME
提问by Svish
When I try to put a value into a DATE field which is invalid, MySQL seems to use 0000-00-00 instead. Is there a way I can do this "check" without updating a DATE field? And to do it from for example PHP?
当我尝试将一个值放入无效的 DATE 字段时，MySQL 似乎使用 0000-00-00 代替。有没有办法在不更新日期字段的情况下进行此“检查”？并从例如 PHP 中做到这一点？
Like, is there a way I can query the MySQL server and ask "Hey, is this DATE, TIME or DATETIME valid to you?"
比如，有没有一种方法可以查询 MySQL 服务器并询问“嘿，这个 DATE、TIME 或 DATETIME 对你有效吗？”
Or is there maybe an even better way of doing it?
或者有没有更好的方法来做到这一点？
采纳答案by VolkerK
If you choose a server mode for the MySQL server that doesn't allow invalid date values a query containing such a malformed date representation will cause an error instead of (silently) assuming 0000-00-00
see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
如果您为 MySQL 服务器选择不允许无效日期值的服务器模式，则包含这种格式错误的日期表示的查询将导致错误，而不是（静默地）假设 0000-00-00，
请参阅http://dev.mysql。 com/doc/refman/5.0/en/server-sql-mode.html
e.g.
例如
$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$pdo->exec('CREATE TEMPORARY TABLE foo (id int auto_increment, d datetime, primary key(id))');
$query = "INSERT INTO foo (d) VALUES ('2010-02-31 12:15:18')";
foreach( array('ALLOW_INVALID_DATES', 'STRICT_ALL_TABLES') as $mode ) {
echo $mode, ": "; flush();
$pdo->exec("SET SESSION sql_mode='$mode'");
$pdo->exec($query);
echo "Ok.\n";
}
prints
印刷
ALLOW_INVALID_DATES: Ok.
STRICT_ALL_TABLES:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2010-02-31 12:15:18' for column 'd' at row 1' in [...]
回答by Abbas
I use this syntax in my Application and it's work like charm!
我在我的应用程序中使用这种语法，它的工作就像魅力一样！
SELECT DATE('2013-09-31') AS valid;
My System is Win7x64 with PHP 5.5 and MySQL 5.6.16
我的系统是带有 PHP 5.5 和 MySQL 5.6.16 的 Win7x64
回答by wimvds
You could parse the date according to the format you want to use and then call checkdate to test if it's a valid date. Make sure you read the comments on http://php.net/manual/en/function.checkdate.php.
您可以根据要使用的格式解析日期，然后调用 checkdate 来测试它是否为有效日期。请务必阅读http://php.net/manual/en/function.checkdate.php上的评论。
回答by Marc B
You could just use a 'constant' query, without using temporary tables and test fields:
您可以只使用“常量”查询，而不使用临时表和测试字段：
mysql> select day('2010-02-31 00:00:00');
+----------------------------+
| day('2010-02-31 00:00:00') |
+----------------------------+
| NULL |
+----------------------------+
回答by BassMHL
Since the question states MySQL, here is a MySQLsolution:
由于问题说明MySQL，这里是一个MySQL解决方案：
It is very difficult to verify if a field is a date because of all the different possible date formats that would need to be taken into account. BUT if you know that the field date formats are one of these:
由于需要考虑所有不同的可能日期格式，因此很难验证字段是否为日期。但是，如果您知道字段日期格式是以下格式之一：
'yyyy-mm-dd'
'yyyy-mm-dd hh:mm:ss'
'yyyy-mm-dd whatever'
This code will help you:
此代码将帮助您：
SELECT count(*) FROM `table`
WHERE DATE(STR_TO_DATE(`column`, '%Y-%m-%d')) IS NOT NULL
AND `column` NOT REGEXP '^[0-9\.]+$'
Basically :
基本上 ：
- the first condition tells you if is a date, but unfortunately doesn't exclude numbers (ex:
DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00' - the second ensures that numbers are excluded, which leaves you with dates only that follow the formats above.
- 第一个条件告诉你 if 是一个日期，但不幸的是不排除数字（例如：
DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00' - 第二个确保排除数字，这使您只能获得遵循上述格式的日期。
If count(*)is >0then it's a date, if it is 0it's something else.
如果count(*)是>0，那就是约会，如果是，0那就是别的东西。
NoteThis method works for strings of any date format as long as you know in advance what format they follow (which I know is not always the case but still helpful). Just replace the format a prioriin the query
注意此方法适用于任何日期格式的字符串，只要您事先知道它们遵循什么格式（我知道情况并非总是如此，但仍然有用）。只需替换a priori查询中的格式
回答by Timo K?hk?nen
SELECT WEEK(col) IS NOT NULL AS valid;
SELECT WEEK(col) IS NOT NULL AS valid;
OR one of the:
或以下之一：
SELECT DAYNAME(col) IS NOT NULL AS valid;
SELECT TO_DAYS(col) IS NOT NULL AS valid;
SELECT TO_SECONDS(col) IS NOT NULL AS valid;
SELECT WEEKDAY(col) IS NOT NULL AS valid;
SELECT WEEKOFYEAR(col) IS NOT NULL AS valid;
SELECT YEARWEEK(col) IS NOT NULL AS valid;
回答by Mubashar Abbas
I do this:
我这样做：
if(strtotime(trim($str)) {
// persist trim($str) in db.
}
回答by Jonatan Lundqvist Medén
To check if a value is a date you can use
day(dateToCheck) = 0
要检查值是否是您可以使用的日期
day(dateToCheck) = 0
回答by PRConnect
Try using this... WHERE DATE_FORMAT(DATE('$value'), '%Y-%m-%d') = '$value'
尝试使用这个... WHERE DATE_FORMAT(DATE('$value'), '%Y-%m-%d') = '$value'
when using Perl, $sth->rows will return zero if it fails
使用 Perl 时，如果失败，$sth->rows 将返回零
while $sth->rows will return nonzero if it succeeds
而 $sth->rows 如果成功则返回非零值
回答by fire
Use this function in php to check for a valid date:
在 php 中使用此函数检查有效日期：
function valid_date($date) {
return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date));
}
Or as @VolkerK said if an invalid date is inserted into the database it will be stored as 0000-00-00 so you could also do:
或者正如@VolkerK 所说，如果将无效日期插入到数据库中，它将被存储为 0000-00-00，因此您也可以这样做：
SELECT * FROM table WHERE date_field > '0000-00-00'
SELECT * FROM table WHERE date_field > '0000-00-00'
