Azure SQL：datetime2转山地标准时间（MST）的简洁实现问询

Got it, let's fix this with a concise, maintainable approach that also handles daylight saving time automatically (a huge plus over manual offset calculations).

First, let's clear up the core issue: when you use [Created] AT TIME ZONE 'Mountain Standard Time' directly on a datetime2 field, SQL Server treats that datetime2 value as already being in Mountain Standard Time and wraps it in a datetimeoffset. That's almost certainly not what you want (unless your Created field is stored in Mountain Time to begin with).

The right concise method depends on what timezone your [Created] value is stored in:

Case 1: [Created] is stored as UTC (recommended practice!) #

Use two AT TIME ZONE calls to first mark the datetime2 as UTC, convert to Mountain Time, then cast to datetime2:

SELECT 
  CAST([Created] AT TIME ZONE 'UTC' AT TIME ZONE 'Mountain Standard Time' AS datetime2) AS MountainCreated
FROM MyTable

Breakdown: #

• [Created] AT TIME ZONE 'UTC': Converts your datetime2 to a datetimeoffset with UTC offset (+00:00), explicitly telling SQL Server "this value is UTC".
• AT TIME ZONE 'Mountain Standard Time': Converts the UTC datetimeoffset to Mountain Standard Time (automatically switches to Mountain Daylight Time when applicable—no manual updates needed for DST changes).
• CAST(...) AS datetime2: Extracts the local datetime value (without the offset) as a clean datetime2 type.

Case 2: [Created] is stored in your server's local timezone #

If your Created field uses the server's local time instead of UTC, replace 'UTC' with your server's timezone (e.g., 'Eastern Standard Time' if your server is in US Eastern Time):

SELECT 
  CAST([Created] AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'Mountain Standard Time' AS datetime2) AS MountainCreated
FROM MyTable

This approach is way cleaner than manual string parsing or offset calculations, and it’s future-proof for daylight saving time adjustments.

内容的提问来源于stack exchange，提问作者tim busfield