1

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.

  • use the limit command to get only one result – Up_One Mar 13 '14 at 15:35
4

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;
| improve this answer | |
  • Isn't the where clause in the wrong place in the second query? – Aaron Bertrand Mar 13 '14 at 18:21
  • @AaronBertrand no, the parenthesis are kind of confusing, C is a derived table. – ypercubeᵀᴹ Mar 13 '14 at 18:23
  • @ypercube ah I see, bad indentation fooled me again – Aaron Bertrand Mar 13 '14 at 18:26
6

Uh, why not just

select /* user.columns, */
       /* company.columns, */
    NumDays = DATEDIFF(Company.CreatedOn, curdate())
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
where Company.CreatedOn < curdate() - INTERVAL 365 DAY;
| improve this answer | |
  • 1
    I'd make that where Company.CreatedOn < curdate() - INTERVAL 365 DAYS ; Reference to the blog of a clever chap – ypercubeᵀᴹ Mar 13 '14 at 18:01
  • @Ypercube changed, thanks, but to be fair I have very little clue about how these things work in MySQL. :-) Was just trying to help with the logic. – Aaron Bertrand Mar 13 '14 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.