I created a custom function to convert UTC date times to the Local DateTime. When doing this I found that the if the field was nothing the textbox expression returned #error.
example expressions that produced error in text box:
Orginal Code with Error:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)
Tried checking if it is a Date first:
=IIF(IsDate(Fields!completed_dttm.Value), Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value), nothing)
Tried checking if the value was nothing and using string values for each output:
=IIF(IsNothing(Fields!completed_dttm.Value), "", Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value.ToString())
I looked for solutions on the internet but didn't find my exact issue.
So, I decided to just try another approach and I within the DataSet, I Added a new Field by right clicking on the dataset and choosing Add...
Name the field, choose Calclulated and enter the original expression from above:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)
Deploying a custom assembly: http://msdn.microsoft.com/en-us/library/ms155034%28v=sql.105%29.aspx
Referencing custom assemblies: http://msdn.microsoft.com/en-us/library/ms155974%28v=sql.105%29.aspx
and: http://msdn.microsoft.com/en-us/library/ms159238%28v=sql.105%29.aspx#Custom
Custom ReportFunctions Class:
using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
namespace RWF.Report.Custom
{
public class ReportFunctions
{
///
/// enum of Time Zone Names
///
public enum TimeZoneName
{
Pacific = 1,
Eastern,
Central,
Mountain,
Arizona
}
///
/// used to translate to local time zones
///
/// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
/// UTC Date/Time
///Local Date/Time public DateTime ToLocalTime(TimeZoneName timeZone, DateTime UtcDateTime)
{
switch (timeZone)
{
case TimeZoneName.Pacific:
return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
case TimeZoneName.Eastern:
return ToLocalTimeFromOffset(UtcDateTime, -5.0, true);
case TimeZoneName.Central:
return ToLocalTimeFromOffset(UtcDateTime, -6.0, true);
case TimeZoneName.Mountain:
return ToLocalTimeFromOffset(UtcDateTime, -7.0, true);
case TimeZoneName.Arizona:
return ToLocalTimeFromOffset(UtcDateTime, -7.0, false);
default:
return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
}
}
///
/// used to translate Local Time to UTC Date Time.
///
/// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
/// Local Date Time
///UTC Date Time public DateTime ToUtcTime(TimeZoneName timeZone, DateTime LocalDateTime)
{
switch (timeZone)
{
case TimeZoneName.Pacific:
return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
case TimeZoneName.Eastern:
return ToUtcTimeFromOffset(LocalDateTime, -5.0, true);
case TimeZoneName.Central:
return ToUtcTimeFromOffset(LocalDateTime, -6.0, true);
case TimeZoneName.Mountain:
return ToUtcTimeFromOffset(LocalDateTime, -7.0, true);
case TimeZoneName.Arizona:
return ToUtcTimeFromOffset(LocalDateTime, -7.0, false);
default:
return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
}
}
///
/// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
///
/// Local Date Time
/// UTC Offset for time zone
/// Time zone observers daylight savings time (DST)
///
public DateTime ToLocalTimeFromOffset(DateTime UtcDateTime, Double Offset, bool ObservesDST)
{
DateTime LocalDateTime;
bool bDateIsInDST;
LocalDateTime = UtcDateTime.AddHours(Offset);
// add 1 to offset if the date is in daylight savings time and it is observed
if (ObservesDST)
{
bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
if (bDateIsInDST)
LocalDateTime = LocalDateTime.AddHours(1.0);
}
return LocalDateTime;
}
///
/// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
///
/// Local Date Time
/// UTC Offset for time zone
/// Time zone observers daylight savings time (DST)
///
public DateTime ToUtcTimeFromOffset(DateTime LocalDateTime, Double Offset, bool ObservesDST)
{
DateTime UtcDateTime;
bool bDateIsInDST;
// add 1 to offset if the date is in daylight savings time and it is observed
if (ObservesDST)
{
bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
if (bDateIsInDST)
Offset = Offset + 1.0;
}
UtcDateTime = LocalDateTime.AddHours(-Offset);
return UtcDateTime;
}
///
/// Returns true if the date falls within DaylightSavingsTime
///
/// Datetime to check
///true/false public bool IsInDaylightSavingsTime(DateTime dateTime)
{
int year = dateTime.Year;
DateTime startDate = GetDaylightBeginDate(year);
DateTime endDate = GetDaylightEndDate(year);
//TimeSpan delta = new TimeSpan(startDate.Ticks - endDate.Ticks);
//DaylightTime daylightTime = new DaylightTime(startDate, endDate, delta);
bool isDST;
//isDST = TimeZone.IsDaylightSavingTime(dateTime, daylightTime);
if ((dateTime > startDate) && (dateTime < endDate))
isDST = true;
else
isDST = false;
return isDST;
}
private DateTime GetDaylightEndDate(int year)
{
if (year < 2007)
{
//last Sunday in October
return GetNextSunday(new DateTime(year, 10, 25, 2, 0, 0));
}
else
{
//first Sunday in November
return GetNextSunday(new DateTime(year, 11, 1, 2, 0, 0));
}
}
private DateTime GetDaylightBeginDate(int year)
{
if (year < 2007)
{
//first Sunday in April
return GetNextSunday(new DateTime(year, 4, 1, 2, 0, 0));
}
else
{
//second Sunday in March
return GetNextSunday(new DateTime(year, 3, 8, 2, 0, 0));
}
}
private DateTime GetNextSunday(DateTime dt)
{
double dow = Convert.ToDouble(dt.DayOfWeek);
if (dow == 0)
{
return dt;
}
else
{
return dt.AddDays(7.0 - dow);
}
}
public DateTime GetUtcDate()
{
return DateTime.UtcNow;
}
}
}
example expressions that produced error in text box:
Orginal Code with Error:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)
Tried checking if it is a Date first:
=IIF(IsDate(Fields!completed_dttm.Value), Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value), nothing)
Tried checking if the value was nothing and using string values for each output:
=IIF(IsNothing(Fields!completed_dttm.Value), "", Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value.ToString())
I looked for solutions on the internet but didn't find my exact issue.
So, I decided to just try another approach and I within the DataSet, I Added a new Field by right clicking on the dataset and choosing Add...
Name the field, choose Calclulated and enter the original expression from above:
=Code.OReportFunctions.ToLocalTime(1, Fields!completed_dttm.Value)
Deploying a custom assembly: http://msdn.microsoft.com/en-us/library/ms155034%28v=sql.105%29.aspx
Referencing custom assemblies: http://msdn.microsoft.com/en-us/library/ms155974%28v=sql.105%29.aspx
and: http://msdn.microsoft.com/en-us/library/ms159238%28v=sql.105%29.aspx#Custom
Custom ReportFunctions Class:
using System;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
namespace RWF.Report.Custom
{
public class ReportFunctions
{
///
/// enum of Time Zone Names
///
public enum TimeZoneName
{
Pacific = 1,
Eastern,
Central,
Mountain,
Arizona
}
///
/// used to translate to local time zones
///
/// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
/// UTC Date/Time
///
{
switch (timeZone)
{
case TimeZoneName.Pacific:
return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
case TimeZoneName.Eastern:
return ToLocalTimeFromOffset(UtcDateTime, -5.0, true);
case TimeZoneName.Central:
return ToLocalTimeFromOffset(UtcDateTime, -6.0, true);
case TimeZoneName.Mountain:
return ToLocalTimeFromOffset(UtcDateTime, -7.0, true);
case TimeZoneName.Arizona:
return ToLocalTimeFromOffset(UtcDateTime, -7.0, false);
default:
return ToLocalTimeFromOffset(UtcDateTime, -8.0, true);
}
}
///
/// used to translate Local Time to UTC Date Time.
///
/// Short Name of time Zone: Pacific, Mountain, Arizona, Central, Eastern
/// Local Date Time
///
{
switch (timeZone)
{
case TimeZoneName.Pacific:
return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
case TimeZoneName.Eastern:
return ToUtcTimeFromOffset(LocalDateTime, -5.0, true);
case TimeZoneName.Central:
return ToUtcTimeFromOffset(LocalDateTime, -6.0, true);
case TimeZoneName.Mountain:
return ToUtcTimeFromOffset(LocalDateTime, -7.0, true);
case TimeZoneName.Arizona:
return ToUtcTimeFromOffset(LocalDateTime, -7.0, false);
default:
return ToUtcTimeFromOffset(LocalDateTime, -8.0, true);
}
}
///
/// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
///
/// Local Date Time
/// UTC Offset for time zone
/// Time zone observers daylight savings time (DST)
///
{
DateTime LocalDateTime;
bool bDateIsInDST;
LocalDateTime = UtcDateTime.AddHours(Offset);
// add 1 to offset if the date is in daylight savings time and it is observed
if (ObservesDST)
{
bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
if (bDateIsInDST)
LocalDateTime = LocalDateTime.AddHours(1.0);
}
return LocalDateTime;
}
///
/// Convert Date/Time to UTC from Local Date/Time, given the offset and observance of daylight savings time
///
/// Local Date Time
/// UTC Offset for time zone
/// Time zone observers daylight savings time (DST)
///
{
DateTime UtcDateTime;
bool bDateIsInDST;
// add 1 to offset if the date is in daylight savings time and it is observed
if (ObservesDST)
{
bDateIsInDST = IsInDaylightSavingsTime(LocalDateTime);
if (bDateIsInDST)
Offset = Offset + 1.0;
}
UtcDateTime = LocalDateTime.AddHours(-Offset);
return UtcDateTime;
}
///
/// Returns true if the date falls within DaylightSavingsTime
///
/// Datetime to check
///
{
int year = dateTime.Year;
DateTime startDate = GetDaylightBeginDate(year);
DateTime endDate = GetDaylightEndDate(year);
//TimeSpan delta = new TimeSpan(startDate.Ticks - endDate.Ticks);
//DaylightTime daylightTime = new DaylightTime(startDate, endDate, delta);
bool isDST;
//isDST = TimeZone.IsDaylightSavingTime(dateTime, daylightTime);
if ((dateTime > startDate) && (dateTime < endDate))
isDST = true;
else
isDST = false;
return isDST;
}
private DateTime GetDaylightEndDate(int year)
{
if (year < 2007)
{
//last Sunday in October
return GetNextSunday(new DateTime(year, 10, 25, 2, 0, 0));
}
else
{
//first Sunday in November
return GetNextSunday(new DateTime(year, 11, 1, 2, 0, 0));
}
}
private DateTime GetDaylightBeginDate(int year)
{
if (year < 2007)
{
//first Sunday in April
return GetNextSunday(new DateTime(year, 4, 1, 2, 0, 0));
}
else
{
//second Sunday in March
return GetNextSunday(new DateTime(year, 3, 8, 2, 0, 0));
}
}
private DateTime GetNextSunday(DateTime dt)
{
double dow = Convert.ToDouble(dt.DayOfWeek);
if (dow == 0)
{
return dt;
}
else
{
return dt.AddDays(7.0 - dow);
}
}
public DateTime GetUtcDate()
{
return DateTime.UtcNow;
}
}
}