Here is an answer written for a specific UK application and based purely on SELECT.

 1. No timezone offset (e.g. UK)
 2. Written for daylight saving starting on last Sunday of March and finishing on last Sunday of October (UK rules)
 3. Not applicable between midnight and 1 AM on the day daylight saving
    starts.  This could be corrected but the application it was written for does not require it.

        -- A variable holding an example UTC datetime in the UK, try some different values:
        DECLARE
        @App_Date datetime;
        set @App_Date = '20250704 09:00:00'

        -- Outputting the local datetime in the UK, allowing for daylight saving:
        SELECT
        case
        when @App_Date >= dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0))))
	        and @App_Date < dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0))))
	        then DATEADD(hh, 1, @App_Date) 
        else @App_Date 
        end