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)