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
Feature Enabled - Cast as date with Oracle
If the feature is enabled, the format depend of the database format.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'
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 :
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 )
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: