KB305437: New behavior in MicroStrategy Secure Enterprise 10.4: Change in default date format for the MicroStrategy ODBC Driver for Amazon Redshift

Beginning with Strategy Secure Enterprise 10.4, the default date format for the Strategy ODBC Driver for Amazon Redshift has changed.  In Strategy Secure Enterprise 10.3 and earlier versions, the default format was "mm/dd/yyyy".  The new default date format is "yyyy-mm-dd".  Running reports or SQL in Strategy 10.4 that use the old date format will result in an error:

ERROR: date/time field value out of range: "21/07/2016" (Hint Perhaps you need a different "datestyle" setting.

• Navigate to Project source→Administration→Configuration Managers→ Database Instances. Right click on the DB instance and select VLDB Properties.
   
• Edit the date format parameter under the select/insert section and set it to yyyy-dd-mm
  

• The reports containing the filter in mm/dd/yyyy format execute successfully.

 
Additionally, free-form SQL reports will need to have any date values manually updated to reflect the new "yyyy-mm-dd" format:
 

 
Method 2 (not preferred as it forces the old-style behavior):
 

• Navigate to the Windows ODBC Administrator and choose to configure the Amazon Redshift DSN. On the Advanced tab, set the value for Initialization String: Set DateStyle='ISO, MDY'
  
  

• A query can now be run successfully with the filter condition set to the 'mm/dd/yy' format