Daylight Savings and GMT Time correction for SSRS

Time Corrections for Reports
Working with a report, we made some interesting discoveries about the time corrections/updates

We are using the following Parameters:
Label: Eastern, Central, Mountain, Pacific
Value: +1, 0, -1, -2
It may also help to set a default value: e.g. -2 for Pacific
Note: It depends on where your data warehouse is located..

If we look at the "DataSet Properties…" of DataSet1
 
We need to go to Parameters:
@Date
@Date1
 
Date:

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Parameters!Date.Value))

Select all Open in new window

Date1:

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Parameters!Date1.Value))

Select all Open in new window

Click the OK Button
We are going to be changing the following..
 

=Fields!Date.Value

Select all Open in new window

Results are: 8/21/2010 9:18:50 AM

If we look up this ticket we see that the time is not correct (off by two hours).
 
So..  We need to change to:

=DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Select all Open in new window

Result are: 8/21/2010 7:18:50 AM

Caution: If you make the change to:

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))

Select all Open in new window

The Entered Date is not correct!
 
Some improvements:

Research..

Date for a Ticket - GMT (Raw Date on the server):

=Fields!Date.Value

Select all Open in new window

Entered Date for a Ticket (adjusted for TimeZone) - GMT:

=Dateadd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Select all Open in new window

Convert UTC to Local Time for an Entry:

=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)

Select all Open in new window

Convert UTC to Local Time (Real Time) for a ticket:

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))

Select all Open in new window

Show real time based on server time - not preferred:

=Dateadd("h",Parameters!TimeZone.Value,Globals!ExecutionTime)

Select all Open in new window

Show real time based on server time - preferred

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Now()))

Select all Open in new window

Alternate

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(DateTime.Now.ToUniversalTime()))

Select all Open in new window

What day of the week was the ticket entered in? - e.g. Sunday

=WeekDayName(DatePart("w",DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))))

Select all Open in new window

What day of the week is it now? - e.g. Monday

=WeekDayName(DatePart("w",DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Now()))))

Select all Open in new window

Alternate

=WeekDayName(DatePart("w",DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(DateTime.Now.ToUniversalTime()))))

Select all Open in new window

What day of the week is it now? - e.g. 2

=DatePart("w",DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(DateTime.Now.ToUniversalTime())))

Select all Open in new window

Alternate

=DatePart("w",DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Now())))

Select all Open in new window

When the ticket was entred in, was it Daylight Savings Time? - Simple version (True or False)

=System.TimeZone.CurrentTimeZone.IsDaylightSavingTime(Fields!Date.Value)

Select all Open in new window

Daylight Savings or Standard Time - Currently? - e.g. "Central Daylight Time"

=IIf(System.TimeZone.CurrentTimeZone.IsDaylightSavingTime(Now()),System.TimeZone.CurrentTimeZone.DaylightName,System.TimeZone.CurrentTimeZone.StandardName)

Select all Open in new window

Daylight Savings or Standard Time - When the ticket was entered in? - e.g. "Central Daylight Time"

=IIf(System.TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))),System.TimeZone.CurrentTimeZone.DaylightName,System.TimeZone.CurrentTimeZone.StandardName)

Select all Open in new window

=WeekDay(Fields!Date.Value)

Select all Open in new window

Results are: 2 (Monday)

=WeekDay(DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)))

Select all Open in new window

Results are: 1 (Sunday)

=Hour(System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))

Select all Open in new window

Results are: 22

=Hour(DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)))

Select all Open in new window

Results are: 20

=Hour(DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)))

Select all Open in new window

Results are: 7

Need to prepend a 0 to the hour of 7?

=Right(100+Hour(DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))),2)

Select all Open in new window

Results are: 07

Was the ticket entered in after-hours?  We are open from 9-5 Pacific and the server is in Central Time..  How do we do that?
Sure, we can do that in the query properties, but what about the Tablix?
1) Create the Tablix with no groupings, etc.
2) Go to Tablix Properties
3) Go to Filters
4) Leave it as Text and "="
5) Value is simply =True (This is very important, go into the Expression Builder if needed)

Let's stop here for a moment and determine what we need..
Is it a Saturday or a Sunday? (WeekDay 7 or 1)
Hour 6 - 5 Pacific is (8 - 7 Central)
Now, let's look at the final code

=((WeekDay(DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)) = 1 OR
                       WeekDay(DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)) = 7) OR
                       (Hour(DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)) >= Parameters!TimeZone.Value+19 OR
                       Hour(DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)) < Parameters!TimeZone.Value+8))

Select all Open in new window

Reference Material..
Guide to Better Reporting – I love this site as there are a treasure trove of good tips here
TimeZoneInfo.ConvertTime Method (DateTime, TimeZoneInfo)
Convert UTC time to local time – Thread that shows from Robert Bruckner about Time Conversion to Real time which got me interested in researching the results below

Now, playing around..
Element: Entered Date for a Ticket from (GMT or database time)
Code Shows as:

=Fields!Date.Value

Select all Open in new window

Results are: 8/2/2010 3:12:34 AM

Element: Entered Date from (Pacific Time - GMT)
Code Shows as:

=Dateadd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Select all Open in new window

Results are:: 8/2/2010 1:12:34 AM

Element: Convert UTC time to local server time for Ticket
Code Shows as:

=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)

Select all Open in new window

Results are: 8/1/2010 10:12:34 PM
Convert UTC time to local time

Element: Convert UTC time to Pacific Time for Ticket (Real Time) - when the ticket was entered in
Code Shows as:

=Dateadd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))

Select all Open in new window

Results are: 8/1/2010 8:12:34 PM
Convert UTC time to local time

Element: Field Change to show month/day
Results are: Sunday, August 01, 2010 8:12 PM

Element: Show Current Time the report is being run from
Code Shows as:

=Dateadd("h",Parameters!TimeZone.Value,Globals!ExecutionTime)

Select all Open in new window

Results are: 9/20/2010 6:11:40 AM

Element: What day of the week was the ticket entered?
Code Shows as:

=WeekDayName(DatePart("w",Dateadd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))),TRUE,0)

Select all Open in new window

Results are: Sunday

Element: Convert UTC time to local server time - Now
Code Shows as:

=System.TimeZone.CurrentTimeZone.ToLocalTime(Now())

Select all Open in new window

Results are: 9/20/2010 8:12:03 AM
Convert UTC time to local time

Element: Convert UTC time to Pacific Time - Now
Code Shows as:

=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Now()))

Select all Open in new window

Results are: 9/20/2010 6:12:03 AM
Convert UTC time to local time

Element: Execution Time (Time report is run)
Code Shows as:

=Globals!ExecutionTime

Select all Open in new window

Results are: 9/20/2010 8:11:40 AM

Element: Execution Time (How long to run report) ?
Code Shows as:

="Execution Time: " +IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", (IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")))

Select all Open in new window

Results are: Execution Time: 22 second(s)
Guide to better Reporting Services

Element: Time report is run - Server Time
Code Shows as:

=DateTime.SpecifyKind(Date.Now, DateTimeKind.Utc)

Select all Open in new window

Results are: 9/20/2010 8:12:03 AM
TimeZoneInfo.ConvertTime Method (DateTime,  TimeZoneInfo)

Element: Time report is run - Server Time
Code Shows as:

=DateTime.SpecifyKind(Date.Now, DateTimeKind.Local)

Select all Open in new window

Results are: 9/20/2010 8:12:03 AM
TimeZoneInfo.ConvertTime Method (DateTime, TimeZoneInfo)

Element: Show the Date/Time Entered in UTC
Code Shows as:

=System.TimeZone.CurrentTimeZone.ToUniversalTime(Fields!Date.Value)

Select all Open in new window

Results are: 8/2/2010 8:12:34 AM

Element: When the ticket was entered, was it Daylight Savings?
Code Shows as:

=System.TimeZone.CurrentTimeZone.IsDaylightSavingTime(Fields!Date.Value)

Select all Open in new window

Results are: TRUE

Element: Utc.Id
Code Shows as:

=System.TimeZoneInfo.Utc.Id

Select all Open in new window

Results are: UTC

Element: BaseUtcOffset - Currently
Code Shows as:

=System.TimeZoneInfo.Local.BaseUtcOffset.Hours

Select all Open in new window

Results are: -6

Element: GetUtcOffset - Currently
Code Shows as:

=System.TimeZoneInfo.Local.GetUtcOffset(Now())

Select all Open in new window

Results are: -05:00:00

Element: Daylight Savings or Standard time? - Currently
Code Shows as:

=IIf(System.TimeZone.CurrentTimeZone.IsDaylightSavingTime(Now()),System.TimeZone.CurrentTimeZone.DaylightName,System.TimeZone.CurrentTimeZone.StandardName)

Select all Open in new window

Results are: Central Daylight Time

Element: DaylightName - From Server
Code Shows as:

=System.TimeZone.CurrentTimeZone.DaylightName

Select all Open in new window

Results are: Central Daylight Time

Element: StandardName - From Server
Code Shows as:

=System.TimeZone.CurrentTimeZone.StandardName

Select all Open in new window

Results are: Central Standard Time

Element: What day number is today?
Code Shows as:

=weekday(today())

Select all Open in new window

Results are: 2