ISDATE() 等效于 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13709744/
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
ISDATE() equivalent for MySQL
提问by Johnny B
I have a line of code for SQL server which takes a date listed as "YYYYMMDD" where the DD is 00 and converts the 00 to 01 so that it works with datetime. I would like to be able to use MySQL for it
我有一行 SQL 服务器代码，它采用列为“YYYYMMDD”的日期，其中 DD 为 00，并将 00 转换为 01，以便它与日期时间一起使用。我希望能够使用 MySQL
the current code which works for SQL server:
适用于 SQL 服务器的当前代码：
INSERT patentdb.Citation(PatentNo, Citation, CitedBy, CitationDate)
SELECT PatentNo, citation, WhoCitedThis, dt
FROM
(
SELECT PatentNo, Citation, WhoCitedThis, dt = CASE
WHEN CitationDate LIKE '%00' THEN INSERT (CitationDate, 8, 1, '1')
ELSE CitationDate
END
FROM patentdb.CitationSource
) AS x
WHERE ISDATE(dt) = 1;
but isdate is not valid in MySQL, what can I do to fix this?
但 isdate 在 MySQL 中无效，我该怎么做才能解决这个问题？
回答by Nick Rolando
You can try using the STR_TO_DATEfunction. It returns nullif the expression is not date, time, or datetime.
您可以尝试使用STR_TO_DATE函数。null如果表达式不是日期、时间或日期时间，则返回。
WHERE STR_TO_DATE(dt, '%d,%m,%Y') IS NOT NULL
回答by Timo K?hk?nen
One possibility, that allows argument to be string, integer or date:
一种可能性，允许参数为字符串、整数或日期：
WHERE DAYNAME(dt) IS NOT NULL
These valid dates return 'Tuesday':
这些有效日期返回“星期二”：
SELECT IFNULL(DAYNAME('2016-06-21 18:17:47') , '');
SELECT IFNULL(DAYNAME('2016-06-21') , '');
These invalid dates return '' (empty string):
这些无效日期返回 ''（空字符串）：
SELECT IFNULL(DAYNAME('0000-00-00 00:00:00') , '');
SELECT IFNULL(DAYNAME('2016-06-32 18:17:47') , '');
SELECT IFNULL(DAYNAME(NULL) , '');
SELECT IFNULL(DAYNAME(10) , '');
It seems that DAYNAME is 2x faster in mysql 5.6 than STR_TO_DATE:
在 mysql 5.6 中，DAYNAME 似乎比 STR_TO_DATE 快 2 倍：
SELECT benchmark(10000000, DAYNAME('2016-06-21 18:17:47'))
1 row(s) returned 3.215 sec / 0.0000072 sec
SELECT benchmark(10000000, STR_TO_DATE('2016-06-21 18:17:47', '%d,%m,%Y'))
1 row(s) returned 7.905 sec / 0.0000081 sec
And I suppose that if the argument is date (rather than eg. string), the performance is better.
我想如果参数是日期（而不是例如字符串），性能会更好。
回答by Bob Nightingale
Similar to Timo K?hk?nen's answer, I've used TIMESTAMPDIFF to determine if a date is valid like ISDATE does. I use the same date in both date parameters. It returns zero if it a date, NULL if not.
类似于 Timo K?hk?nen 的回答，我使用 TIMESTAMPDIFF 来确定日期是否像 ISDATE 那样有效。我在两个日期参数中使用相同的日期。如果是日期则返回零，否则返回 NULL。
I ran all three examples with BENCHMARK with valid and invalid dates. I ran this on a shared server from the ISP JustHost, MYSQL version 5.6.32-78.1:
我用有效和无效日期的 BENCHMARK 运行了所有三个示例。我在来自 ISP JustHost 的共享服务器上运行了这个，MYSQL 版本 5.6.32-78.1：
SELECT benchmark(10000000, DAYNAME('2016-06-21 18:17:47'));
-- 1 row(s) returned 3.215 sec / 0.0000072 sec
Mine: Query took 3.5333 seconds.
SELECT benchmark(10000000, STR_TO_DATE('2016-06-21 18:17:47', '%d,%m,%Y'));
-- 1 row(s) returned 7.905 sec / 0.0000081 sec
Mine: Query took 7.9635 seconds.
SELECT benchmark(10000000, TIMESTAMPDIFF(DAY,'2016-06-21 18:17:47','2016-06-21 18:17:47'));
Mine: Query took 5.1373 seconds.
...........................
With bad date (June 41st?)
SELECT benchmark(10000000, DAYNAME('2016-06-41 18:17:47'));
Mine: Query took 7.3872 seconds.
SELECT benchmark(10000000, STR_TO_DATE('2016-06-41 18:17:47', '%d,%m,%Y'));
Mine: Query took 7.9919 seconds.
SELECT benchmark(10000000, TIMESTAMPDIFF(DAY,'2016-06-41 18:17:47','2016-06-41 18:17:47'));
Mine: Query took 7.3792 seconds.
STR_TO_DATE is slightly slower than the other two. The DAYNAME method seems the fastest if you are working mostly with valid dates. But none is truly a bad way to go.
STR_TO_DATE 比其他两个稍慢。如果您主要使用有效日期，那么 DAYNAME 方法似乎是最快的。但没有一个是真正的坏路。
回答by bonCodigo
You could also use the following
您还可以使用以下
Using a REGEXP'^([1-9]|0[1-9]|1[012])/([1-9]|0[1-9]|[12][0-9]|3[01])/(19|20)[0-9][0-9]'
用一个 REGEXP'^([1-9]|0[1-9]|1[012])/([1-9]|0[1-9]|[12][0-9]|3[01])/(19|20)[0-9][0-9]'
But regexp can be tricky here though, as there too many different format for a date,
但是 regexp 在这里可能很棘手，因为日期有太多不同的格式，
Or
或者
Cast DATE and then check for length of result, found null, then not date.
length(DATE(mydate))
投射日期，然后检查结果的长度，发现空值，然后不是日期。
length(DATE(mydate))
回答by user13501194
Well, while I appreciate all statements, I always like it the easy way.
好吧，虽然我很欣赏所有的陈述，但我总是喜欢简单的方式。
All date stamps either have the "-" or "/" characters in them, so why not check all date columns that have such characters, using the LIKE argument.
所有日期戳中都有“-”或“/”字符，所以为什么不使用 LIKE 参数检查所有具有此类字符的日期列。
SELECT * FROM TABLE WHERE DATE_COLUMN LIKE '%/%';
SELECT * FROM TABLE WHERE DATE_COLUMN LIKE '%-%';
