SQL Subquery / Function in Group By

I ran into an issue this week where I had written a table function (ITVF) to help convert a datetimeoffset column from UTC to the destination timezone within a query. Generally, we handle all date conversion at the application layer using simple .NET DateTime / TimeZone functions, but in this case we were trying to group by the adjusted date to display a count of events on a particular date. If the GROUP BY was run against the raw data, the resulting set would be grouped by the UTC timezone. As such, we needed to convert the timestamp to the destination timezone before performing the grouping.

When I first wrote the query, I attempted to simply group by the ITVF, but the following error message was thrown:

Msg 144, Level 15, State 1, Line 7
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

My first solution to this involved rewriting the core components of the ITVF statement directly into my query, but this made for an unwieldy and long query. Then last night I had an epiphany – why not just create the converted dataset as a view/subquery. I did a bit of quick research and to simplify my needs with integrating this timezone adjustment into an existing query, I decided on a Common Table Expression (CTE). The CTE allows us to define an expression that can be reused throughout the rest of the query similar to a table or view. The resulting solution is as follows:

DECLARE @timezone varchar(255) SET @timezone = 'Central Standard Time'; /*.NET Timezone ID*/  WITH AH AS (     SELECT ID, AttributeID, Value, Status, /* original columns, plus the converted _time */       (select * from TimezoneOffset.dbo.fn_ConvertToTimezoneTBL(@timezone, Time)) as TimeAdj     FROM [Alarms].[dbo].[History] )  select CONVERT(Date, TimeAdj, 1) as DateAdj, count(Value) from AH group by CONVERT(Date, TimeAdj, 1)