Return to Revisions

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(d, 1 - datepart(dw, 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(d, 1 - datepart(dw, 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