Convert GETDATE() into local time

Below statement will give server time zone

DECLARE @TimeZone VARCHAR(50)

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone AS [Server time zone]


SELECT GETDATE(), GETUTCDATE()

Below statement gives current date into location time as per country time zone

SELECT GETDATE() AT TIME ZONE 'Central Europe Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'W. Australia Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'Cen. Australia Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'AUS Central Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'E. Australia Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'AUS Eastern Standard Time' AT TIME ZONE 'UTC'

SELECT GETDATE() AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC'