Why is GetDate AND GETDATE() AT TIME ZONE 'GMT Standard Time' returning the wrong time?

Please see the code below:

DECLARE @UKDateTime as DateTime
SELECT @UKDateTime = GETDATE() AT TIME ZONE 'GMT Standard Time' 
print @UKDateTime 
PRINT GETDATE()

This returns:

Oct  4 2021  4:03PM
Oct  4 2021  4:03PM

The time in the UK is currently: 5:03pm (as confirmed by my PC clock). It appears to be returning the UTC date. What is the problem?

I have SQL Server setup as a Docker container. It is SQL Server v18.4.

The documentation for AT TIME ZONE says:

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

You're using GETDATE, which returns a datetime without any offset information, so the function assumes it is a time in the target time zone already.

If you want to convert the local server date & time to GMT, you could use:

SELECT SYSDATETIMEOFFSET() 
    AT TIME ZONE 'GMT Standard Time';

Or for example, if your local server time zone is 'Eastern Standard Time':

SELECT GETDATE()                         -- datetime without context
    AT TIME ZONE 'Eastern Standard Time' -- where you are
    AT TIME ZONE 'GMT Standard Time';    -- target time zone

For more on AT TIME ZONE, see AT TIME ZONE – a new favourite feature in SQL Server 2016 by Rob Farley.

StackExchange Database Administrators Q#300593, answer score: 12