Change the timezone for date/time - Arizona

Base on my understanding, there are few key points to resolve your question:

• Convert column data to the datetime type.
• How to use the datetimeoffset type to store datetime and timezone info.
• Use AT TIME ZONE to convert time zones.
• What exactly is the time zone of Arizona.

The converting just like your code: 

SELECT DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))

The datetime type doesn't have time zone information, so it's difficult to handle DST conversions. SQL Server provides the datetimeoffset type to store datetime and timezone. You can use AT TIME ZONE to append a time zone to the datetime type and convert it to datetimeoffset type.

AT TIME ZONE function of SQL Server will treat input as target time zone when the input parameter doesn't have time zone info(such as the datetime type). For example, execute 

SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time'

will return

2010-02-01 12:00:00.000 -05:00

When the input contains time zone information, the function converts it into the target time zone using the time zone conversion rules. The target time zone uses the Windows time zone name. The system view sys.time_zone_info contains all the time zones supported by SQL Server. It has a row with a name column has value 'US Mountain Standard Time' and is_current_dst column value is 0, dose that means this time zone can be considered Arizona time? To confirm this, run tzutil /l in the Windows command line, In the list, UTC-07:00 shows that the Arizona time zone name is 'US Mountain Standard Time', which matches the row from sys.time_zone_info，it doesn't use daylight saving time. So what we need to do is call AT TIME ZONE like this:

SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'

Now, full code is here:

SELECT DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))

AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'