MySQL query to count days in date range with start and end date

To count days in date range, you need to find the difference between dates using DATEDIFF().

Let us first create a table:

mysql> create table DemoTable730 (
   StartDate date,
   EndDate date
);
Query OK, 0 rows affected (0.45 sec)

Insert some records in the table using insert command:

mysql> insert into DemoTable730 values('2019-01-21','2019-07-21');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable730 values('2018-10-11','2018-12-31');
Query OK, 1 row affected (0.46 sec)
mysql> insert into DemoTable730 values('2016-01-01','2016-12-31');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement:

mysql> select *from DemoTable730;

This will produce the following output -

+------------+------------+
| StartDate  | EndDate    |
+------------+------------+
| 2019-01-21 | 2019-07-21 |
| 2018-10-11 | 2018-12-31 |
| 2016-01-01 | 2016-12-31 |
+------------+------------+
3 rows in set (0.00 sec)

Following is the query to count days in date range:

mysql> select ABS(DATEDIFF(StartDate,EndDate)) AS Days from DemoTable730;

This will produce the following output -

+------+
| Days |
+------+
| 181  |
| 81   |
| 365  |
+------+
3 rows in set (0.00 sec)