Search code examples
mysqldateperiod

mysql difference in month as column in where clause

need your help. I tried myself but cant solve it.

I have a table with date column (ex. birth) So i need to select all rows where birth is in past more than one months.

I googled and found a SO question similar The difference in months between dates in MySQL

But there is no a condition by this new period. My wrong query is: 

select id,period_diff(date_format(now(), "%Y%m"), date_format(birth, "%Y%m")) as months 
from t1
where months>0 
limit 10
Solution

  • Try Below:

    use having (ALIAS months in WHERE Clause not working here)

    select id,period_diff(date_format(now(), "%Y%m"), date_format(birth, "%Y%m")) as months 
from t1
having months>0 
limit 10

    HAVING is like WHERE but it is able to work on columns which are computed (Alias). HAVING works by pruning results after the rest of the query has been run - it is not a substitute for the WHERE clause.