AKAWN

/* find your time zone name */
SELECT [name] FROM sys.time_zone_info ORDER BY [name];

/* New Zealand Standard Time example 1 */
SELECT 
 GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS local_time_datetimeoffset
,CAST(GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_time_datetime
,CONVERT(char(19),GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' , 121) AS local_time_char;

/* New Zealand Standard Time example 2 */
SELECT 
 GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS local_time_datetimeoffset
,CAST(GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_time_datetime;

/* New Zealand Standard Time example 3 */
SELECT
 session_id
,command
,CAST(start_time AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_start_time
,percent_complete AS percent_completed
,CASE
 WHEN percent_complete <> 0
 THEN CAST(DATEADD(SECOND,estimated_completion_time/1000,GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time') AS datetime) END
 AS local_estimated_completion_time
FROM sys.dm_exec_requests
ORDER BY percent_complete;