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

要获取当前日期和日期字段之间的天数，语法如下所示 −

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)
   -> );
Query OK, 0 rows affected (0.55 sec)

使用 insert 命令在表中插入一些记录。查询如下所示 −

mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-10-2011');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('01-12-2013');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('31-10-2014');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-04-2016');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('20-08-2018');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DateDifferenceDemo(ArrivalDate) values('11-03-2019');
Query OK, 1 row affected (0.07 sec)

使用 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)

更新于：2019-07-30

116 次浏览

开始您的 职业生涯

完成课程获得认证

开始
广告