Need help in writing a Case statement to get the 'US Eastern Standard Time' from the sys.time_zone_info system view in SQL Server. There is an error in the Select statement and cannot figure out where. The CN.CreateDate is currently in UTC time and need to convert to EST taking into account Daylight Savings time.

select 
CN.CreateDate
,case when (SELECT * FROM sys.time_zone_info TZI WHERE    
TZI.name = 'US Eastern Standard Time'
        AND ISNULL(TZI.is_currently_dst,0)=0) 
        THEN DATEADD(hour, -5,CN.CreateDate) 
        ELSE DATEADD(hour, -4,CN.CreateDate) 
        END AS CreateDateEST
from MC_CDCPContactNotes CN

CodePudding user response：

Just to fix your query:

select 
  CN.CreateDate
  ,case when (SELECT 
                 count(*) 
                 FROM sys.time_zone_info TZI 
                 WHERE TZI.name = 'US Eastern Standard Time'
                 AND ISNULL(TZI.is_currently_dst,0)=0) = 1 
    THEN DATEADD(hour, -5,CN.CreateDate) 
    ELSE DATEADD(hour, -4,CN.CreateDate) 
    END AS CreateDateEST
 from MC_CDCPContactNotes CN

But I would rewrite it like:

select 
  CN.CreateDate
  ,dateadd(hour,-5 ISNULL(TZI.is_currently_dst,0),CN.CreateDate) as CreateDateEst 
from MC_CDCPContactNotes CN
join sys.time_zone_info TZI 
    on TZI.name = 'US Eastern Standard Time'

CodePudding user response：

This functionality is built into SQL Server with AT TIME ZONE, there's no need to re-invent the wheel here. Only thing to note is that you may need to convert to UTC then to your required zone first, e.g.

WITH MC_CDCPContactNotes AS 
(   SELECT  t.CreateDate
    FROM (VALUES (('20221225 00:00'), ('20220625 00:00')) AS t (CreateDate)
)
SELECT  CN.CreateDate,
        EasternTime = cn.CreateDate AT TIME ZONE 'UTC' AT TIME ZONE  'US Eastern Standard Time'
FROM    MC_CDCPContactNotes CN;

Which gives:

Example on db<>fiddle