Mysql – Datediff() in MySQL

datefunctionsMySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below.

select datediff((select curdate()),
(select Company.CreatedOn
from
dbname.company
inner join
dbname.user 
on
user.company_id = company.company_id
))

I am getting the result as follows,

Error Code: 1242 Subquery returns more than 1 row

I do understand the error, but I can't use any id's to point out as follows,

select datediff
(
(select curdate()),
(select Company.CreatedOn
from
dbname.company
inner join
dbname.user
on
user.company_id = company.company_id
where
company.company_id = 17)
);

While I run this query I got the output as follows,

38

I need to select the users depends on the company which registered time exist more than 365 days.

Need to display all the company which registered time more than 365 days, I have not mentioned this condition in code,

I need to solve Subquery result, datediff function should compare with all the company CreatedOn value and results only the companies which exists more than 365 days.

Any help would be grateful.

Best Answer

Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this

SELECT
   user.*,
   company.*.
   DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);

You could take further and refactor the query as follows:

SELECT
   U.*,
   C.*.
   DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;
Related Question