获取当前日期和日期字段之间的天数？
要获取当前日期和日期字段之间的天数，语法如下：
SELECT DATEDIFF(CURDATE(),STR_TO_DATE(yourColumnName, '%d-%m-%Y')) AS anyAliasName FROM yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table DateDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate varchar(100) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-10-2011');
mysql> insert into DateDifferenceDemo(ArrivalDate) values('01-12-2013');
mysql> insert into DateDifferenceDemo(ArrivalDate) values('31-10-2014');
mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-04-2016');
mysql> insert into DateDifferenceDemo(ArrivalDate) values('20-08-2018');
mysql> insert into DateDifferenceDemo(ArrivalDate) values('11-03-2019');使用select语句显示表中的所有记录。查询如下-
mysql> select *from DateDifferenceDemo;
这是输出-
+----+-------------+ | Id | ArrivalDate | +----+-------------+ | 1 | 12-10-2011 | | 2 | 01-12-2013 | | 3 | 31-10-2014 | | 4 | 12-04-2016 | | 5 | 20-08-2018 | | 6 | 11-03-2019 | +----+-------------+ 6 rows in set (0.00 sec)
以下是获取当前日期和日期字段之间的天数的查询-
mysql> SELECT DATEDIFF(CURDATE(),STR_TO_DATE(ArrivalDate, '%d-%m-%Y')) AS NumberOfDays from DateDifferenceDemo;
这是输出-
+--------------+ | NumberOfDays | +--------------+ | 2708 | | 1927 | | 1593 | | 1064 | | 204 | | 1 | +--------------+ 6 rows in set (0.00 sec)