>>To be precise, HOUR() is not T-SQL function.
>
>Yes, but do you know anything about this DAX reference. Is that an add-on to SQL Server to provide additional functionalities, a 3rd party we need to purchase or else?

These functions are used in SQL Server Analysis Server (SSAS) or you can use them in Power Pivot. You can not use them in traditional T-SQL code you use in SSMS when connecting to the Database Engine.