I do agree with @Thom A's comment that the problem is not with your query that even I have tried the same query in Azure SQL database, and it gave me the expected output.

Based on the output image that you have provided, I can say that you are previewing data from an Azure Data factory or synapse pipeline by giving the above query in copy activity or lookup activity query option.

In the activity data preview of ADF pipeline, I got the same results as yours upon trying your query.

In ADF, AFAIK, the activity output or previews from query option of the activity only follows the UTC even though you have provided the correct query. I came to this conclusion after trying the same query in the lookup activity and it gave the same result in the activity output as well.

But apart from the activity outputs and data previews it will convert the values to required time zones. I have added the above SQL query in a copy activity source and used a sample delimited as target. Upon executing the copy activity, you can see the values converted to expected time zone in the target csv file. It's the same result for the SQL table target as well.

So, it won't affect the data when you copy the above data to any target. But if you want use activity outputs in the pipeline, you need to convert the values to required time zones explicitly using ADF dynamic expressions.