在select语句的SQL 2012中获取正确的时区

Question

运行以下查询时，我需要获取此SQL查询以输出正确时区的时间。

SELECT 
     CAST([StartDateTime] AS time(0)) AS 'time' 
     from appointment

当我运行这个程序时，它当前以UTC时间返回所有内容，我需要EST。我尝试过switchoffset命令，它很有效，但我只需要时间来显示，而不需要日期和时间。

Answer 1

这应该有效：

SELECT CAST(DATEADD(HH,-5,[StartDateTime]) as time) AS 'time'
FROM appointment

Answer 2

试试这个

DECLARE @appointment TABLE
  (
    id integer,
    StartDateTime datetime
  )
INSERT INTO @appointment SELECT 1, '1/1/2014 13:30'
INSERT INTO @appointment SELECT 2, '1/4/2014 4:00'
INSERT INTO @appointment SELECT 3, '1/6/2014 15:30'
INSERT INTO @appointment SELECT 4, '1/8/2014 23:00'
SELECT 
   CAST(SWITCHOFFSET(CONVERT(DATETIMEOFFSET, StartDateTime), '+01:00') as TIME(0)) as StartDateTime
FROM @appointment