2 - Articles Related

3 - The big mistake

One big mistake that is made with the date, is that people may confuse between :

the format of a date

and the data type of a date.

Why ? Because a lot of database include an implicit datatype transformation from a string into a date. See this example below on Oracle :

sh@orcl>SELECT DAY_NAME FROM times WHERE time_id ='01-JAN-95';
DAY_NAME
---------
Sunday

Oracle take the string '01-JAN-95' transform it as a date and perform the query.

But what happen if you change the format of the date with the NLS_DATE_FORMAT parameter because you are in a multi-language environment :

sh@orcl>ALTER SESSION SET NLS_DATE_FORMAT ='YYYY/MM/DD';
Session altered.
sh@orcl>SELECT DAY_NAME FROM times WHERE time_id ='01-JAN-95';SELECT DAY_NAME FROM times WHERE time_id ='01-JAN-95'*
ERROR AT line 1:
ORA-01858: a non-numeric character was found WHERE a numeric was expected

You fired an error because Oracle expected an other date format to be able to transform it as a date data type.

To be able to support the localization, you must send to the database not a string but a real value with a date data type. You can do that with the TO_DATE function in Oracle.

sh@orcl>SELECT DAY_NAME FROM times WHERE time_id =TO_DATE('01-JAN-1995','DD-MONTH-YYYY');
DAY_NAME
---------
Sunday

It works !

Then especially when you work in a multi-language environment, you always must set in a filter not a formatted string but a real value with a date data type. The DATE function of the OBIEE logical Sql have this purpose.