Different time format use in OBIEE 11g

OBIEE Timestamp

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS'
TIMESTAMP '1990-01-10 13:35:43'

Time stamp difference in obiee 
I have two columns with TIMESTAMP data type(One is In time and the other is Outtime).The values are as below.
In Time Out Time
14-jan-08 1:40:45 AM             14-jan-08 1:30:45 PM
15-jan-08 3:22:53 PM             15-jan-08 5:23:54 PM
16-jan-08 3:20:50 PM             16-jan-08 4:23:54 PM
17-jan-08 3:13:44 PM             17-jan-08 7:23:54 PM

I need the out put as below
00:11:50:00
00:2:01:01
00:1:03:04
00:4:10:10

Solution:
CAST( TimestampDiff(SQL_TSI_DAY, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_HOUR, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_MINUTE, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_SECOND, timestamp1, timestamp2) AS CHAR(2))

Convert VARCHAR2 to TIMESTAMP in Oracle

DATE and TIMESTAMP are two different datatypes. So you need to use the correct conversion function for each, which in your case would be TO_TIMESTAMP().

SELECT TO_TIMESTAMP('14-SEP-11 12.33.48.537150 AM', 'DD-MON-RR HH:MI:SS.FF AM')

FROM DUAL;

select from_tz(to_timestamp('14-SEP-11 12.33.48.537150 PM', 'DD-Mon-RR HH.MI.SS.FF AM'), 'EUROPE/LONDON') from dual

TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)

RETURN TIMESTAMP

-- convert a character string to a timestamp. The character 

-- string is not in the default TIMESTAMP format, so the 

-- format mask must be specified:

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')

   FROM DUAL;

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')

---------------------------------------------------------------------------

10-SEP-02 02.10.10.123000000 PM

I have direct query and when error is occured

i see presentation variables replaced with value in format yyyy-mm-dd.

part of the sql:

POLISA.DATUM_IZDANJA <= cast(TimestampAdd(SQL_TSI_MONTH, -12, cast(DATE '2013-06-19' as TIMESTAMP)) as date))) AND (POLISA.DATUM_IZDANJA BETWEEN date '2013-06-01' AND date '2013-06-19') 

1. Using evaluate function we can modify the format.

ex:

USING EVALUATE

cast(Evaluate('to_char(%1,%2)',"Inventory"."Catalog"."dbo"."Dim_W_DAY_D_Inv_Unallocated_Run_Month"."CALENDAR_DATE",'YYYYMon') as varchar(10))

cast(Evaluate('to_char(%1,%2)',<<COLUMN HERE>>,'YYYY-Mon') as varchar(10))

2. Open your report then click column properties then select Data format then select custom then here you can put which format you want.

I can suggest you one things, Please you can try this.

1. Use instead of to_char you can use to_date and remove the cast statement in the condition.

Problem starts when you click on the button apply of the prompt, then obiee transfers date column into sql statement in format yyyy-mm-dd.

My default obiee date format is dd.mm.yyyy

When I pickup date from calendar date on prompt is shown as dd.mm.yyyy, but when I click apply that date goes in format yyyy-mm-dd into sql statement.

I want when I click on apply to keep dd.mm.yyyy format of the date.

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 :