TO_DAYS() Date query for difference in days of MySQL fields

Returns difference in days between 0 date and the date given.

select TO_DAYS('2016-08-22');

Output is 736563

Syntax

TO_DAYS(date)

Example of such a application is to find out the days a guest has stayed in a hotel, we have to take the difference of arrival date and departure date. Both the fields are date and time fields.

SELECT '2023-08-22' as Checkin  , '2023-08-23' as Checkout,  (TO_DAYS( '2023-08-23')-TO_DAYS( '2023-08-22') )as No_of_Days;

Output

Checkin	Checkout	No_of_Days
2023-08-22	2016-08-23	1

SELECT '2016-08-22' as Checkin  , '2023-08-23' as Checkout,  (TO_DAYS( '2023-08-23')-TO_DAYS( '2016-08-22') )as No_of_Days

Output is here

Checkin	Checkout	No_of_Days
2016-08-22	2023-08-23	2557

Another example is in library if we are finding out the difference in days between date of issue and date of return. You can get many such applications where difference in days are required. Here is the sql query applied to a MySQL table and the result is shown. You can get the sql dump of the table at the end of this tutorial.

SELECT To_days( dt2 ) - TO_DAYS( dt ) FROM `dt_tb`

PHP Code with Query

We will create one PHP page with this query for easy understanding of the application.

MySQLi database connection string

<?Php
require "config.php";// Database connection

$query="SELECT id,dt,dt2,(To_days( dt2 ) - TO_DAYS( dt )) as difference FROM `dt_tb`";

if($stmt = $connection->query("$query")){

  echo "No of records : ".$stmt->num_rows."<br>";

  echo "<table class='table table-striped'>
<tr class='info'> <th> id</th><th> dt</th><th>dt2</th><th>Difference</th></tr>";
while ($row = $stmt->fetch_assoc()) {
 echo "<tr><td>$row[id]</td><td>$row[dt]</td><td>$row[dt2]</td><td>$row[difference] </td></tr>";
 }
echo "</table>";
}else{
echo $connection->error;
}
?>

The output of this query is here

id	dt	dt2	difference
1	2004-10-26 00:00:00	2005-01-25	91
2	2004-05-05 23:56:25	2005-06-12	403
3	2005-12-08 13:20:10	2005-06-06	-185

Here is the sql code to create and fill the table with records

CREATE TABLE IF NOT EXISTS `dt_tb` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dt2` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `dt_tb`
--

INSERT INTO `dt_tb` (`id`, `dt`, `dt2`) VALUES
(1, '2004-10-26 00:00:00', '2005-01-25'),
(2, '2004-05-05 23:56:25', '2005-06-12'),
(3, '2005-12-08 13:20:10', '2005-06-06');

SQL Date References MONTH() DAY() NOW() : Current Date with Time

Subscribe to our YouTube Channel here

Tamilselvi.k	12-03-2009
query for find orderdate ,15 days after today's date from sales_order table.

mishal	16-12-2009
select Sysdate 15 from dual; this is useful to find 15 days after today's date.

maharajan	23-02-2017
Thank you very much. I exactly looking for this code. :)

TO_DAYS(): Difference in days between two date fields of a record

PHP Code with Query

Subscribe