>>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?

Michel, similar to what Naomi mentioned, HOUR is a function in DAX. DAX is the expression language for PowerPivot data models. (PowerPivot is used for "self-service" BI models created in Excel - the PowerPivot engine uses a subset of the SSAS Tabular technology)

You probably noticed that HOUR is color-coded in the SSMS, which adds to the confusion, as several functions documented in MSDN (but not related to SQL Server) wind up being color coded in SSMS. Maybe there is a way to modify the color coding, but I'm not sure.

In any event, the DATEPART function is what you want to use in SQL Server.