OBIEE - Cast as date - Which date format to use ?

Bi Server Architecture With Client

About

When you use the cast function to transform a character data type as a date, you may struggle with the date format that you have to give in a formula column because it depends of the configuration.

Be careful, you don't have the same date format to give if you use a OBIEE - Date Format in presentation variable, dashboard prompt and logical SQL

The date format is dependent of the query cache, you must clear it first if you want to see the new format

The configuration of the date format

The date format depend if the cast feature is enabled in the database. (Property of the database in the physical layer / Tab Features).

Obiee Database Cast Feature

For instance by default in a XML database the feature is not enabled and in an Oracle Database it is.

Feature Enabled - Cast as date with Oracle

If the feature is enabled, the format depend of the database format.

For Oracle, it's the NLS_DATE_FORMAT session parameter and you can change it for instance by performing an alter statement in the “execute on connect” capabilities of the connection pool :

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'

Obiee Connexion Alter Nls Date Format

The conversion of datetime values uses format models specified in the parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT, depending on the particular datetime data type.

Here the manipulation in Plsql :

C:\Documents and Settings\Nicolas>sqlplus SH/SH

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 25 11:07:32 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sh@orcl>select sysdate from dual;

SYSDATE
---------
25-JUN-09

sh@orcl>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';

Session altered.

sh@orcl>select sysdate from dual;

SYSDATE
----------
2009/06/25

sh@orcl>select cast('2009/06/01' as date) from dual;

CAST('2009'
----------
2009/06/01

Feature Not Enabled - Cast as date with BI Server

If the feature is not enabled, the BI server will perform it based on the the date format specified in the nqsconfig, by default, you have :

DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss" ;
DATE_DISPLAY_FORMAT = "yyyy/mm/dd" ;
TIME_DISPLAY_FORMAT = "hh:mi:ss" ;

This is the format use to show a date from a date local variable for instance

Obiee Date Character Format Internal

How to know the format that OBIEE use ?

Apart the fact that you can find it by checking the OBIEE configuration, an easy way to discover it is to create a formula column in a answer with a statement like this one :

cast(Calendar."Time Id" as CHAR)

The idea is by casting a date column as CHAR, we can see the date format :

When the cast depend of the database :

Obiee Cast Oracle

The date format is dependent of the query cache, you must clear it first if you want to see the new format

When the cast is performed by the BI server :

Obiee Cast Obiee

Example

This function which calculate the number of a week in a month will work in a XML database when the cast is performed by the BI Server and will not work for an default Oracle Database configuration because the default format is 01-MAR-1998.

mod(WEEK_OF_YEAR("D0 Time"."T00 Calendar Date"),
    CASE WHEN month("D0 Time"."T00 Calendar Date") >= 10  
      THEN WEEK_OF_YEAR( cast( cast(year("D0 Time"."T00 Calendar Date") as char) || '/' || 
                         cast(month("D0 Time"."T00 Calendar Date") as char) || '/01' as date))
      ELSE WEEK_OF_YEAR( cast( cast(year("D0 Time"."T00 Calendar Date") as char) || '/0' || 
                         cast(month("D0 Time"."T00 Calendar Date") as char) || '/01' as date))  
    END
)

Support

If you use a format in a Oracle Database which is not the same than define in the database, you will have this kind of errors :

ORA-01843: not a valid month. nQSError: 16015 SQL statement execution failed. (HY000)

[nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1861, 
message: ORA-01861: literal does not match format string at OCI call OCIStmtExecute:

Documentation / Reference





Discover More
Bi Server Architecture With Client
OBIEE - Date Datatype and Functions

This article contains all information and articles the date datatype in OBIEE. The SQL 92 standard defines three kinds of 'typed' datetime literals, in the following formats: To express...
Obiee Ceim
OBIEE - Functions

The home page of OBIEE functions of the logical sql and from BI Server. See Logical SQL Reference
Obiee Repository Variable Date Type
OBIEE - How to set and use a server variable (system/repository) of type date ?

This article is a tutorial on how to set a date in a server variable (session variable of repository variable) and use it. This article use the schema SH : SH repository installation in the database and...



Share this page:
Follow us:
Task Runner