当前日期和日期字段之间的天数

时间:2022-08-14 01:26:55

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.

如果有人可以提供帮助我有这个问题。我的表(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

这是对的吗?谁能帮我?先谢谢你

3 个解决方案

#1


8  

You can use STR_TO_DATE():

您可以使用STR_TO_DATE():

SELECT DATEDIFF(CURDATE(),STR_TO_DATE(date, '%m/%d/%Y')) AS days
FROM table1

SQLFiddle Demo

#2


1  

Your DATE field should have DATE or DATETIME format to be used as DATEDIFF argument correctly.

您的DATE字段应具有DATE或DATETIME格式,以便正确用作DATEDIFF参数。

Also DATE is MySQL keyword and I am not sure that you can use it as valid field name.

此外,DATE是MySQL关键字,我不确定您是否可以将其用作有效字段名称。

#3


0  

You can use this for accurate result

您可以使用它来获得准确的结果

SELECT DATEDIFF(CURDATE(), DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(`date`)), '%Y-%m-%d')) AS days FROM `table1`

#1


8  

You can use STR_TO_DATE():

您可以使用STR_TO_DATE():

SELECT DATEDIFF(CURDATE(),STR_TO_DATE(date, '%m/%d/%Y')) AS days
FROM table1

SQLFiddle Demo

#2


1  

Your DATE field should have DATE or DATETIME format to be used as DATEDIFF argument correctly.

您的DATE字段应具有DATE或DATETIME格式,以便正确用作DATEDIFF参数。

Also DATE is MySQL keyword and I am not sure that you can use it as valid field name.

此外,DATE是MySQL关键字,我不确定您是否可以将其用作有效字段名称。

#3


0  

You can use this for accurate result

您可以使用它来获得准确的结果

SELECT DATEDIFF(CURDATE(), DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(`date`)), '%Y-%m-%d')) AS days FROM `table1`