Working with dates in OBIEE

Here are several options to set date formats in OBIEE. Option #1 is the best way to work with dates and comparison of dates to strings containing dates in a particular format.

See also: http://gerardnico.com/wiki/dat/obiee/cast_as_date

The most important settings (with example values) are:

• DATE_DISPLAY_FORMAT parameter in nqsconfig.ini: YYYY/MM/DD
• DATESHORTFORMAT parameter in localedefinitions.xml (based on the locale set in nqsconfig.ini): M/D/YYYY
• NLS_DATE_FORMAT parameter of the session (or database default): DD-MON-RR

Do not confuse these with parameters in dbfeatures.ini. There this format is the default for almost all database platforms in parameter DATE_FORMAT parameter.

Method 1 is used for implicit casting only if physical -> Database -> Features: CAST_SUPPORTED is available and enabled. Otherwise the settings described in method 4 (nqsconfig.ini) are used.

1) Tell Oracle how to interpret the casting of a string to date or vice versa (preferred method)

Explicitly set the Oracle NLS property for this to work unambiguously. This property can be set on a database level, affecting all connections made to the database. Alternatively, the property can also be set on the OBI connection in the physical layer of the Admin tool to only affect the current session:

• Double-click "Connection pool" for the Oracle connection
• Select "Connection Scripts" tab
• Add "Execute on Connect" script and type:
  ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'

Then, use the following function in Answers formulae:

CAST('20110215' TO DATE)

Note: to check the value of the NLS_DATE_FORMAT parameter at the database level, e.g. from SQL Developer, execute the following statement:

SELECT * FROM (

SELECT 'instance' as domain, parameter, value FROM NLS_INSTANCE_PARAMETERS UNION

SELECT 'database' as domain, parameter, value FROM NLS_DATABASE_PARAMETERS UNION

SELECT 'session' as domain, parameter, value FROM NLS_SESSION_PARAMETERS

) x WHERE PARAMETER ='NLS_DATE_FORMAT'

2) Convert a date to string or vice versa using an Oracle database function

Use the OBIEE function EVALUATE to make use of an Oracle database function. In this case, TO_DATE:

Convert string to date:
EVALUATE('TO_DATE(%1,%2)' AS DATE,'20111231','YYYYMMDD')

Examples of what doesn't work:
EVALUATE('TO_DATE(%1)' AS DATE,'20100101')

--> does not work, unless the date happens to be in the nls parameter format

CAST('20110215' TO DATE) 

--> does not work, unless the string happens to be in the nls parameter format

Convert date to string:
EVALUATE('TO_CHAR(%1,%2)' AS CHAR,DIM_DATE.TODAY,'YYYYMMDD')

EVALUATE('TO_CHAR(%1,''YYYYMMDD'')' AS CHAR,DIM_DATE.TODAY)

Examples of what doesn't work:
Conversions may fail with "date format not recognized" or "datetime value does not match the specified format".

EVALUATE('TO_CHAR(%1,%2)',DIM_DATE.TODAY,'YYYYMMDD')
--> does not work, because the result needs to be explicitly cast to a text datatype

EVALUATE('TO_CHAR(%1)',DIM_DATE.TODAY)
--> does not work, unless the date happens to be in the nls parameter format

3) Convert a date to string using different OBIEE functions

An alternative method is to convert the date to a string in stead of converting a testing string to a date. The difference is this:

DATE = ConvertToDate(STRING) 

vs 

ConvertToString(DATE) = STRING

Convert date to number:

year(FACT_ORDERLINE.DATE) * 10000 + month(FACT_ORDERLINE.DATE) * 100 + dayofmonth(FACT_ORDERLINE.DATE) = 20110231

Note: here the lefthand and righthand side are numbers, not strings, but the idea is the same.

4) Convert a string to date using the OBIEE function "DATE"

In OBIEE, an equivalent of the Oracle function TO_DATE is the DATE function which has a fixed syntax:

YYYY-MM-DD. 

For example:

DATE '2011-01-15'

Note:

• This syntax cannot be configured and does not depend on locale or nls settings
• The function cannot be found in the list of functions when adding a formula in Answers. 
• It does not use ( or ) signs, but only single-quotes

5*) Set uniform datatypes in the Physical Layer

Set the datatype property in the physical layer on the fields/attributes. Allowed date/time datatypes are:

• DATE
• TIMESTAMP

Try to use one, e.g. DATE, as the default to achieve a more universal way of working with dates. TIMESTAMP is required when there is an essential time part. If it is always "00:00:00", then use DATE.

6*) Locale definitions in configuration files

• [bipath]\web\config\localedefinitions.xml
  -- displays possible formats to choose from
  -- locale "us-en" is based on locale: us
  -- locale "us" contains all possible attributes to choose from, so is independend of the regional settings
  -- of the Windows server
  locale: us-en
• [bipath]\server\config\nqsconfig.ini
  -- for displaying messages
  -- this maps to "us-en" in localedefinitions.xml
  LOCALE = "English-usa";
  -- for input and output of dates
  DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss";
  DATE_DISPLAY_FORMAT = "yyyy/mm/dd";
  TIME_DISPLAY_FORMAT = "hh:mi:ss";
  -- when on, obi does not guess what a string containing a date means
  STRONG_DATETIME_TYPE_CHECKING = ON;

7*) Locale definitions in Answers webinterface

Set the locale, e.g. "English - United states" on the connection window or in your account settings: Settings -> Account

8*) Save display format

The default display format of a date is set in dbfeatures.ini (?)

It can be overriden for all date datatypes or specific attributes in Answers:

Criteria -> Column properties -> Save -> As the system-wide...

Test casting of date to string

Example: database or session NLS parameter = 'YYYYMMDD'

CAST(DIM_DATE.TODAY AS CHAR)
CAST('20110211' AS DATE)
CAST('01-FEB-11' AS DATE) --> will fail if nls parameter is set to YYYYMMDD

Notes:

• Column properties -> date format (i.e.date or text) is set automatically after the result of a conversion. It does not steer, in any way, the input of functions.