MySQL 当前日期和日期字段之间的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17179850/
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
Number of days between current date and date field
提问by user1951561
I have this problem if anyone can help.
There is a field (date)in my table (table1)that is a date in the format 3/31/1988 (M/D/y), and my necessity is to define how many days have passed since that date.
如果有人可以提供帮助，我有这个问题。(date)我的表(table1)中有一个字段是格式的日期3/31/1988 (M/D/y)，我需要定义自该日期以来已经过去了多少天。
I have tried to give this instruction
我试图给出这个指令
SELECT DATEDIFF(CURDATE(), date) AS days
FROM table1
But it gives back 'null' and I think this happens because the two date formats are different (CURDATE() is YMD.....
但它返回 'null'，我认为这是因为两种日期格式不同（CURDATE() 是 YMD .....
Is it correct? can anyone help me? Thank you in advance
这是正确的吗？谁能帮我？先感谢您
回答by DarkAjax
You can use STR_TO_DATE():
您可以使用STR_TO_DATE()：
SELECT DATEDIFF(CURDATE(),STR_TO_DATE(date, '%m/%d/%Y')) AS days
FROM table1
回答by zavg
Your DATEfield should have DATEor DATETIMEformat to be used as DATEDIFFargument correctly.
您的DATE字段应该具有DATE或DATETIME格式才能DATEDIFF正确用作参数。
Also DATEis MySQL keyword and I am not sure that you can use it as valid field name.
也是DATEMySQL 关键字，我不确定您是否可以将其用作有效的字段名称。
回答by Joy Chowdhury
You can use this for accurate result
您可以使用它来获得准确的结果
SELECT DATEDIFF(CURDATE(), DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(`date`)), '%Y-%m-%d')) AS days FROM `table1`
回答by Waiseman
If you want to consider results without - signs that you have to follow parameters position as below :
如果要考虑没有 - 标志的结果，则必须遵循以下参数位置：
SELECT DATEDIFF(Big_Date,Small_Date) AS days FROM table1.
positive results e.g 5 (with no sign), if you place a Small date as the first parameter then it will results minus sign e.g -5.
正结果，例如 5（无符号），如果您将小日期作为第一个参数，那么它将产生负号，例如 -5。
