Using an alias in a where clause in MySql-CodePudding

I've this table structure:

HDN_Client	HDN_Type	HDN_EndDate
Client #1	Hosting	2022-02-01
Client #2	Hosting	2022-03-01
Client #3	Hosting	2022-07-30

I want to display the list of datas when the HDN_EndDate has 60 or less days between the today date and the HDN_EndDate date.

My query is the following one:

SELECT *, 
DATEDIFF(HDN_EndDate, '2022-01-17') 
    AS HDN_DateDiff 
FROM ___table 
WHERE HDN_Type='Hosting' 
   AND HDN_DateDiff<60 
ORDER BY HDN_DateDiff ASC

But it says that the HDN_DateDiff can't be used in the WHERE clause.

How can I make it work?

CodePudding user response：

You can't refer to an alias in a WHERE clause defined in the same SELECT. MySQL overloads its HAVING clause to allow it to use aliases. The following version should work:

SELECT *, DATEDIFF(HDN_EndDate, '2022-01-17') AS HDN_DateDiff
FROM ___table
WHERE HDN_Type = 'Hosting'
HAVING HDN_DateDiff < 60
ORDER BY HDN_DateDiff;