KB14133: The display format for a date attribute does not change even after changing the SQL Date Format and Date Pattern settings in MicroStrategy Developer

SYMPTOM:
The display format for dates does not change even after changing the SQL Date format and Date Pattern settings under VLDB properties of the project in Strategy Developer. This occurs when the data type of the attribute is timestamp.
For example, if the user sets the date format under VLDB properties as "yyyy/mm/dd", then the SQL for the report will appear as shown below:
select to_date('DAY_DATE', 'dd/mm/yyyy') as DAY_DATE
from LU_DAY
Strategy SQL Generation Engine does not recognize the VLDB settings for the date.
 
STEPS TO REPRODUCE:
Follow the steps below to reproduce this issue:
 

• Under Project Configuration, go to Database Instance -> VLDB Properties -> Select/Insert -> check the SQL Date Format and Date Pattern, as shown below:
  

•  
  SQL Date Format should be set to the required date format and the Date Pattern setting should be set to 'to_date(#0,'required date format')'
   
  For example, SQL Date format = yyyy/mm/dd and Date Pattern = to_date(#0,'yyyy/mm/dd')
   
  
• Check the regional settings for date. Go to Control Panel -> Regional Options -> Date and the short date format should be set to the desired date display format, as shown below:
  

• Check the column alias of the date attribute ID form. The column alias is set to data type 'Timestamp'.

CAUSE:
The two advanced VLDB settings, SQL Date format and Date Pattern, are applied only to attributes of the type 'date'. They will not apply to 'datetime' or 'timestamp' data types.
 
ACTION:
Create a new column alias for the date attribute ID form with the data type 'date'. Now the report execution will return the date in the desired format.
 
Follow the steps below to change the column alias to data type 'date':
 

• In the attribute editor, select the attribute ID form and select 'Modify'.
• Go to the 'Column Alias' tab and select 'Modify'.
• In the 'Column Editor - Column Selection' window, select 'New'.
• In the 'Column Editor - Definition' window, enter a column name and choose the Data type as 'Date', as shown below: