KB20288: How to Display a Null Value in Date Format in MicroStrategy 10.x

SYMPTOM:
Sometimes, when there is an attribute with a description form that uses a date data type, if some of the description elements are null, it becomes important to display said null values with a date format, usually in the form of a dummy date. For instance, "display all the null date values in the data warehouse as a dummy date of 31-DEC-9999".
 
ACTION:
This issue can be solved in two steps using a Strategy 10.x pass-through function, in this case the ApplySimple function.
 

1. Convert the string into a date format on the database side as shown below
   to_date( string1, )
   
• string1 is the string that will be converted to a date.
• format_mask is optional. This is the format that will be used to convert string1 to a date.
2. Use a conditional clause, such as a Case statement, to handle the null values:
   to_char (case when Date_DESC is null then to_date(`31-DEC-9999`,`DD-MON-YYYY`)
   
3. The final ApplySimple, placed in the Attribute Form definition, would then look like this:
   ApplySimple( "(case when #0 is null then to_date(`31-DEC-9999`,`DD-MON-YYYY`",)
   

NOTE: The syntax shown in this document is for Oracle and is only presented for illustration purposes. Please contact your Database Administrator for further assistance in getting the correct syntax for your database.