Having a hard time with time zones

• tom 69406

  SSC-Addicted

  Points: 446

  More actions

  July 16, 2021 at 4:28 pm

  Go to Answer

  #3910724

  I found a glitch in one of my reports.

  Its due to events spanning the hour difference when a time zone moves to daylight saving

  So 31st March 2019 was a Sunday, and being the last Sunday in March - we advance at 01:00 to 02:00 - so the time 01:30 cannot exist on 31st March 2019.

  So how do a perform a DATEADD to take account of this?

  -- original data supplied as text, and is converted as follows
  DECLARE @CallDateTime DATETIME = '20190331 00:08:13'
  DECLARE @Duration NVARCHAR(25) = '01:18:04'
  DECLARE @DurationMS FLOAT
  SET @DurationMS = CAST(DATEDIFF(MS, 0, CAST(@Duration AS time)) AS float)
  
  -- report then shows the End Time, using the @DurationMS
  SELECT @CallDateTime AS CallDateTime,
  @DurationMS AS DurationMilliseconds,
     CONVERT(Time, DATEADD(MILLISECOND, @DurationMS, @CallDateTime AT TIME ZONE 'GMT Standard Time')) AS EndTime,
     CONVERT(Time, DATEADD(MILLISECOND, @DurationMS, @CallDateTime)) AS EndTime2
  
  

  EndTime *should* be 02:26:17

  Can anyone enlighten me on how to do this?

   

• Steve Collins

  SSCommitted

  Points: 1686

  More actions

  July 16, 2021 at 7:00 pm

  Answer

  #3910997

  You could try it like this

  DECLARE @CallDateTime DATETIME = '20190331 00:08:13'
  DECLARE @Duration NVARCHAR(25) = '01:18:04'
  
  declare @t          datetime=@duration;
  select @t+@CallDateTime at time zone 'GMT Standard Time';

  (No column name)
  2019-03-31 02:26:17.000 +01:00

  Convert both values to datetime first and then add them together and SELECT AT TIME ZONE

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können