Convert string to date or timestamp

Sometimes it is necessary to obtain a date from the "non-standard" text string and use it directly in the filter or in the calculation.Example: To view data for previous 24 months from the month of '2009 / 01 '. It is a simple filter, where time we want to limit by:

1. Convert string to date
2. Subtracting 24 months.

Conversion:
The Oracle database would be trivial using to_date ('2009 / 01 ',' YYYY / MM ') or to_timestamp ('2009 / 01', 'YYYY / MM'). OBI EE directly provides only two way to make text date or timestamp and using functions:

1. DATE 'YYYY-MM-DD HH: MM: SS' and TIMESTAMP 'YYYY-MM-DD HH: MM: SS'
2. CAST ('nls_date_format' as date) and CAST ('nls_timestamp_format' as timestamp)

Thus, the input string in the first case can not be used because it is not in the format YYYY-MM in the latter case we do not know how they are set nls formats. If you use the date and timestamp is not possible to pass as a parameter to another function that would be carried out replacement '- 'with' / 'and therefore easy conversion to a date more complicated. Workaround: use native database functions called by evaluate (see. document Oracle BI Server and Embedded DB Functions): cast (evaluate ('add_months (to_date (''@{ month} {2010/01} / 01'','' YYYY / MM / DD''),- 24) 'as date) as date)

Leading or Trail Space Trim Issue

If you have column-values with leading or trail spaces those space will be trimmed by presentation services by default, if you drilldown to detail, will lead to no-results or wrong records.

Suppose in your column only these values are there ‘abc’, ‘abc ’ (spaced value),’xyz ’ (spaced value) if you drill on ‘abc’ you will get proper records of 'abc', if we drill on ‘ abc’ will display ‘abc’ records but not ‘ abc’ records, if we drill on ‘xyz ‘ then it will say no results.

To avoid these problems, change column’s data format property to “Plain text (don’t break spaces)” by this property presentation services will not trim leading or trail spaces.

Even in OTN once or twice asked this question, John and Stijn have suggested very good solutions.

I have faced same issue today, I cannot apply my logic because column selected from column selector, so went with Stijn's recommendation.