SQL Reporting Services Daylight saving time query

Question

I have been tasked with writting a report from MS Dynamic CRM. The report contains appointment and account information. I am using Visual Studio 2005 and SQL 2005.

The problem I have found is that if an appointment has a scheduled start date that falls after Britsh Summer Time but has a creation date before (BST) then the ScheduledStart field of the dbo.Appointment DB udates with the offset time + or - on hour. This means when I look for an appointment using reporting services and key in the time the appointment is due to start it returns no results because in the DB the time is either one hour earlier or later. I can manually amend the time for my search but this then returns the incorrect time on the report.

This will of course ony happen twice a year as appointments are only arranged a couple of weeks in advance but it is still a pain!

Is there a way using Transact SQL (or any method availbale to SSRS) I can allow for daylight saving times so even though the DB shows a Sceduled Start (dbo.Appointment.ScheduledStart) of say 11:00:00 the appointment is actually due to start at 10:00:00?

Answer 1

CREATE FUNCTION OffsetBST (@CreatedDateTime   DATETIME,
                           @ScheduledDateTime DATETIME)
RETURNS DATETIME
AS
  BEGIN
      DECLARE @InDateYear VARCHAR(4)
      DECLARE @StartDay VARCHAR(2)
      DECLARE @EndDay VARCHAR(2)
      DECLARE @BSTStart DATETIME
      DECLARE @BSTEnd DATETIME
      DECLARE @OffsetDateTime DATETIME

      SET @StartDay = Convert(VARCHAR(2), (31 - (5 * Year(@CreatedDateTime)/4 + 4) % 7))
      SET @EndDay = Convert(VARCHAR(2), (31 - (5 * Year(@CreatedDateTime)/4 + 1) % 7))
      SET @InDateYear = Convert(VARCHAR(4), Year(@CreatedDateTime))
      SET @BSTStart = Convert(DATETIME, @InDateYear + '-03-' + @StartDay + ' 01:00:00', 20)
      SET @BSTEnd = Convert(DATETIME, @InDateYear + '-10-' + @EndDay + ' 00:00:00', 20)

      IF (@CreatedDateTime BETWEEN DATEADD(yy, DATEDIFF(yy,0,@ScheduledDateTime), 0) AND @BSTStart)
         AND (@ScheduledDateTime BETWEEN @BSTStart AND @BSTEnd)
        SET @OffsetDateTime = Dateadd(hh, 1, @ScheduledDateTime)
      ELSE
        SET @OffsetDateTime = Dateadd(hh, 0, @ScheduledDateTime)

      RETURN @OffsetDateTime
  END

You can use this in your data source select statement. You will provide it with the created date and scheduled date and it will determine if the scheduled date requires a BST offset.

Like so:

SELECT A.Appointment_ID, A.Appointment_Name, dbo.OffsetBST(A.Created_Date, 
A.Scheduled_Date) as Scheduled_Date
FROM AppointmentsTable as A

Answer 2

Alternately this case statement will work inside your existing query.

SELECT CASE
     WHEN ([Created_Date] BETWEEN 
     Dateadd(yy, Datediff(yy, 0, [Scheduled_Date]), 0) 
     AND 
     Convert(DATETIME, Convert(VARCHAR(4), Year([Created_Date])) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year([Created_Date])/4 + 4) % 7)) + ' 01:00:00', 20))
          AND 
          ([ScheduledDate] BETWEEN 
          Convert(DATETIME, Convert(VARCHAR(4), Year([Created_Date])) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year([Created_Date])/4 + 4) % 7)) + ' 01:00:00', 20) 
          AND 
          Convert(DATETIME, Convert(VARCHAR(4), Year([Created_Date])) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year([Created_Date])/4 + 1) % 7)) + ' 00:00:00', 20))
     THEN Dateadd(hh, 1, [Scheduled_Date])
     ELSE [Scheduled_Date]
   END AS [Scheduled_Date]