Search code examples
mysqlsqldatediff

Using AND with datediff

I'm sure this is an easy fix, but I just can't find it anywhere I look. I am trying to use AND to affect the results of my query. Query below.

select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
from table1
where log_in < date_opened;

So that first query works fine. It gives me only the rows where the log_in date is less than date_opened and then the total days difference between the two, which is great. However, I am looking to add an AND to the query to exclude certain totals. For example below:

select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
from table1
where log_in < date_opened and date_diff > 1;

Problem is the alias column date_diff is not recognized as a real column, so I get an error message. I tried using HAVING instead of AND but that didn't work (not that I thought it would). I basically want to exclude the rows that have zero. Anyone has any idea on what I'd use instead of 'and date_diff > 1'?

Solution

  • Either repeat the expression in your where clause (that is, use datediff(log_in, date_opened) > 1 instead of date_diff > 1), or use a derived table, and add the condition on the enclosing query.

    Example of using derived table:

    select first_name, log_in, date_opened, date_diff
from (
    select first_name, log_in, date_opened, datediff(log_in, date_opened) as date_diff
    from table1
    where log_in < date_opened
)
where date_diff > 1