<

Go Premium for a chance to win a PS4. Enter to Win

x

Daylight Savings and GMT Time correction for SSRS

Published on
16,431 Points
10,231 Views
2 Endorsements
Last Modified:
Time Corrections for Reports
Working with a report, we made some interesting discoveries about the time corrections/updates

We are using the following Parameters:
Starting Entered Date (Date) formatted as Data type: "Date/Time"
Ending Entered Date (Date1) formatted as Data type: "Date/Time"
TimeZone formatted as Data type: "Text"
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..
Default Values
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))

Open in new window


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

Open in new window


Click the OK Button
We are going to be changing the following..
 
=Fields!Date.Value

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)

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))

Open in new window

The Entered Date is not correct!
 
Some improvements:
Be able to display a desired TimeZone like Pacific Time and not the server time in GMT, e.g. -8
With Microsoft Access and Microsoft Excel, for example: We can use KERNEL32 to get the user's system time/date

Research..

Date for a Ticket - GMT (Raw Date on the server):
=Fields!Date.Value

Open in new window


Entered Date for a Ticket (adjusted for TimeZone) - GMT:
=Dateadd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Open in new window


Convert UTC to Local Time for an Entry:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)

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))

Open in new window


Show real time based on server time - not preferred:
=Dateadd("h",Parameters!TimeZone.Value,Globals!ExecutionTime)

Open in new window


Show real time based on server time - preferred
=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Now()))

Open in new window


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

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))))

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()))))

Open in new window


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

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())))

Open in new window


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

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)

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)

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)

Open in new window


=WeekDay(Fields!Date.Value)

Open in new window

Results are: 2 (Monday)

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

Open in new window

Results are: 1 (Sunday)

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

Open in new window

Results are: 22

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

Open in new window

Results are: 20

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

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)

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))

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

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)

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)

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))

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)

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)

Open in new window

Results are: Sunday

Element: Convert UTC time to local server time - Now
Code Shows as:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Now())

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()))

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

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)", "")))

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)

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)

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)

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)

Open in new window

Results are: TRUE

Element: Utc.Id
Code Shows as:
=System.TimeZoneInfo.Utc.Id

Open in new window

Results are: UTC

Element: BaseUtcOffset - Currently
Code Shows as:
=System.TimeZoneInfo.Local.BaseUtcOffset.Hours

Open in new window

Results are: -6

Element: GetUtcOffset - Currently
Code Shows as:
=System.TimeZoneInfo.Local.GetUtcOffset(Now())

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)

Open in new window

Results are: Central Daylight Time

Element: DaylightName - From Server
Code Shows as:
=System.TimeZone.CurrentTimeZone.DaylightName

Open in new window

Results are: Central Daylight Time

Element: StandardName - From Server
Code Shows as:
=System.TimeZone.CurrentTimeZone.StandardName

Open in new window

Results are: Central Standard Time

Element: What day number is today?
Code Shows as:
=weekday(today())

Open in new window

Results are: 2
2
Comment
Author:Kent Dyer
0 Comments

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Suggested Articles

In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month