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
Articles Related
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).
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'
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
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 :
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 :
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: