SQL Server 2008 UTC转换为EST

Question

我需要转换一个日期列，数据库中的日期正好偏离了5个小时。时间保持为UTC，当他们查看应用程序时，它会显示EST时区。应用程序自动转换时间。UTC比东部标准时间提前了5秒。以下代码似乎为大多数时间戳固定了时间和日期，但是，有没有办法设置和偏移？报告回顾一天，并应将转换后的UTC时间返回到EST时间。

SELECT DISTINCT 
    Encounters.EncounterNo, MedicalRecords.MedRecNo as MRN,
    DATEADD(DAY, -1, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), Documents.CreateDateTime)) AS [Create Date],
    SUBSTRING(Enrollees.EnrolleeName, 1, CHARINDEX(',', Enrollees.EnrolleeName) - 1) as [Last Name],
    SUBSTRING(Enrollees.EnrolleeName, CHARINDEX(',', Enrollees.EnrolleeName) + 1, len(Enrollees.EnrolleeName)) as [First Name], 
    DocTypes.DocTypeName, 
    CAST(Enrollees.BirthDate AS Date) AS [BIRTH DATE], 
    Enrollees.Gender, Enrollees.SocSecNo, Enrollees.Address, 
    CAST(Encounters.EncntrStartDate AS Date) AS [ADMIT DATE]
FROM            
    Enrollees 
INNER JOIN
    MedicalRecords ON Enrollees.EnrolleeOwnerId = MedicalRecords.EnrolleeOwnerId 
INNER JOIN
    Documents 
INNER JOIN
    DocTypes ON Documents.DocType = DocTypes.DocType 
INNER JOIN
    DocsOwners ON Documents.DocId = DocsOwners.DocId 
INNER JOIN
    Encounters ON DocsOwners.OwnerId = Encounters.EncntrOwnerId 
    ON MedicalRecords.MedRecOwnerId = Encounters.MedRecOwnerId
WHERE
    Documents.DocType = '65' 
    AND (DATEDIFF(DD, GETUTCDATE(), Documents.CreateDateTime) = - 1)
ORDER BY 
    DocTypes.DocTypeName

Answer 1

一个简单的方法是创建一个计算列，然后让报表引用这个新列。这样，您可以保留原始数据，并通过数据库处理转换。

ALTER TABLE [Your Table Name] ADD
UTC2EST  AS dateadd(hh,-5,[Your current UTC datetime column])