Historical data logs in Webreach\Diagrams timeout or take a long time to load

Issue
Historical queries in WebReach take a long time to run, or time out with the following error:

• Microsoft OLE DB Provider for SQL Server error '80040c8e'
  Error with provide HTML. Error with Generate HTML. WebReachHistoricalASP in GetQuery: -2147218290 Cannot execute default connection: An error occurred in Execute: Timeout expired
  /ion/Historical/GetLogs.asp, line 58

PMEv8.0 Error: Error with GenerateHTML: Query timeout expired

The Calendar control is being used to pick a date range.
The pre-defined date ranges (i.e. 'Yesterday' radio button, 'Last Month') returns results quickly with no errors.

Product Line
ION Enterprise 6.x (ION)
Struxureware Power Monitoring 7.x (SPM)
Power Monitoring Expert 7.x (PME)
Power Monitoring Expert 8.0

Environment
Historical Data Log viewers in WebReach

Cause
One of the changes made for Localization purposes was to change all SQL queries to use region independent date formats. (with the "T" separator)
For some reason, for the changes made to the date format for WebReach queries, LSProvider seems to interpret these timestamps as having Milliseconds, even when they don't.

The date format being used by the Calendar control causes a huge extra WHERE clause to be added to the SQL query being executed.
This additional WHERE clause slows down the query exponentially.

Resolution

*Warning: Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop).

The date format returned from the Calendar control can be edited for WebReach so as to not cause LSProvider to append the milliseconds WHERE clause in the resulting SQL.

For ION Enterprise 6.x / StruxureWare Power Monitoring 7.0.x / Power Monitoring Expert 7.2.x:
1. Navigate to following file in directory below and open for editing:

ION Enterprise 6.x: \ION Enterprise\system\webreach\scripts\historical\DateRange.asp
StruxureWare Power Monitoring 7.0.x: \StruxureWare Power Monitoring\system\webreach\historical\DateRange.aspx
Power Monitoring Expert 7.2.x: \Power Monitoring Expert\system\webreach\historical\DateRange.aspx

2. Find the function: CreateWhereClause()
3. Inside the function, you'll see fromDateQuery, toDateQuery: you will see that it's sticking a "T" into the date string:

fromDateQuery = cal_gen_date_query(fromDate.value.substring(0,fromDate.value.indexOf(" "))) + "T" +
fromDate.value.substring(fromDate.value.indexOf(" ") + 1);
toDateQuery = cal_gen_date_query(toDate.value.substring(0,toDate.value.indexOf(" "))) + "T" +
toDate.value.substring(toDate.value.indexOf(" ") + 1);

4. Overwrite the "T" so it's just inserting a space " ":

fromDateQuery = cal_gen_date_query(fromDate.value.substring(0,fromDate.value.indexOf(" "))) + " " +
fromDate.value.substring(fromDate.value.indexOf(" ") + 1);
toDateQuery = cal_gen_date_query(toDate.value.substring(0,toDate.value.indexOf(" "))) + " " +
toDate.value.substring(toDate.value.indexOf(" ") + 1);

The changes should get picked up next time the web page is loaded.

Without that "T" in the datestrings, LSProvider won't build up all the millisecond stuff in the query and it should be the same speed as yesterday() etc.

For Power Monitoring Expert 8.0

1. Navigate to the file in the Power Monitoring Expert folder: \system\webreach\historical\DateRange.aspx
2. Search for  word "altSeparator" replace the "T" for " "
3. Save the file and execute iisreset from Command Prompt

The changes should get picked up next time the web page is loaded.

Without that "T" in the datestrings, LSProvider won't build up all the millisecond stuff in the query and it should be the same speed as yesterday() etc.

NOTE: The workaround for PME 8.0 will not work on Power Monitoring Expert 8.1. Please contact Technical Support for additional information.