Issue in writing a SQL statement

I have a MySQL table called Hotel. in that i have the following fields ID,hotelName,Address,YearOfEstablishment.

The YearOfEstablishment is type Date and it'll store a date as 2010-12-26. What i need to do is to find the difference between the dates, and the SQL for that is as follows;

S开发者_StackOverflow社区ELECT DATEDIFF('2011-08-08','2010-07-26');

The answer for the above query will be given in days, like for example the difference between the 2 dates might be 120 days, I need to show this in Years. Like to divide the value 120 by 365 and give the number of years. How do i write the SQL statement for this?

2.) Now i need to write another SQL, that would show all the Hotel table columns and the Year difference values (that is explained above). How do i write the SQL for this?

(I am using MySQL)

1. SELECT DATEDIFF('2011-08-08','2010-07-26') / 365;
2. SELECT ID,hotelName,Address,YearOfEstablishment, DATEDIFF(YearOfEstablishment,'2010-07-26') / 365 AS years FROM Hotel;

However, be sure to know that an average year has 365.2425 days.

And if your reference datetime is now, simply use NOW(): SELECT DATEDIFF('2011-08-08',NOW()) / 365;.

You should be able to use a query like this:

SELECT ID, hotelName, Address, YearOfEstablishment, (DATEDIFF(YearOfEstablishment, '2010-07-26')/365) AS differenceInYears

Try this

SELECT MOD (DATEDIFF('2011-08-08','2010-07-26'), 365) ;