MYSQL中不完整日期的操作 [英] Operations on incomplete dates in MYSQL

问题描述

我需要在 2 个日期上执行操作(在本例中为 DATEDIFF()).但至少其中一个日期是不完整的.

i need to perform an operation (in this case DATEDIFF()) on 2 dates. But at least one of those dates is incomplete.

例如:DATEDIF('2010-01-00','2010-02-01') OR DATEDIF('2010-01-00','2010-02-00') 将返回NULL

For example: DATEDIF('2010-01-00','2010-02-01') OR DATEDIF('2010-01-00','2010-02-00') will return NULL

确实，在 http:///dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff，手册，它说 DATEDIF 不能处理不完整的日期，因为所有的日期算术函数.

Indeed, in http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff, the manual, it says DATEDIF can't handle incomplete dates, as all the date arithmetic functions.

因为我不能，不想改变我的数据库，我真的需要做这个操作，你知道一种方法，一个技巧吗?

As i can't,don't want to change my database and i really need to do this operation, do you know a way to do it, a trick?

即使我的实际问题是执行此 DATEDIF，我也会问一般情况.

I ask in general even if my actual issue is to perform this DATEDIF.

推荐答案

您可以使用 IF 和 CONCAT 检查不完整的日期并将 00 替换为 01.

You can use IF and CONCAT to check for incomplete dates and replace 00 with 01.

IF ( isnull(CONCAT(v6.title,v7.title,v8.title)), IF ( isnull(CONCAT(v7.title,v8.title)), v8.title, 
        CONCAT(v7.title,\'.\',v8.title) ),
        CONCAT(v6.title,\'.\',v7.title,\'.\',v8.title) ) AS "Datum",
        IF ( isnull(CONCAT(v9.title,v10.title)) & isnull(CONCAT(v11.title, v12.title)), v13.title, 
                   IF ( isnull(CONCAT(v9.title,v10.title)) & isnull(v13.title), CONCAT(v11.title,\'-\', v12.title),
                    IF ( isnull(CONCAT(v11.title,v12.title)) & isnull(v13.title), CONCAT(v9.title,\':\', v10.title, \' Uhr\'),
                    "Hello World" ) ) ) AS "Uhrzeit",

这篇关于MYSQL中不完整日期的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！