TO_DAYS(): Difference in days between two date fields of a record
Returns difference in days between 0 date and the date given.
Output is 736563
Difference in date between two date columns in a table can be found out by converting the columns by using to_days function. After converting that can be simply subtracted to get the difference.
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 '2016-08-22' as Checkin , '2016-08-23' as Checkout, (TO_DAYS( '2016-08-23')-TO_DAYS( '2016-08-22') )as No_of_Days
Output is here
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.
The basic query is here
SELECT To_days( dt2 ) - TO_DAYS( dt ) FROM `dt_tb`
We will create one PHP page with this query for easy understanding of the application.
The sql query is modified to display all the columns with the day difference for easy comparison. Here is the query used.
$query="SELECT id,dt,dt2,(To_days( dt2 ) - TO_DAYS( dt )) as difference FROM `dt_tb`";