Difference between dates

ORA-00666

Hi.

I need to calculate difference between colum BIRTH_DATE (type is datetime) and parameter @{p_date_till}.

This is what I've tried:

1. TIMESTAMPDIFF(SQL_TSI_YEAR ,  BIRTH_DATE, Cast(Cast(@{p_data_iki} as Char) as Timestamp))

--It gives correct formula, but in results tab gives error:

ORA-01847: day of month must be between 1 and last day of month at OCI call OCIStmtFetch.

2. TIMESTAMPDIFF(SQL_TSI_YEAR ,  BIRTH_DATE, @{p_data_iki} )

--It gives incorrent formula:

Function TimestampDiff is called with an incompatible type.

--Also when I ignore formula error and press results tab I get the same error.

--But when I open analysis in view mode (open from catalog) and type value 2016-01-01 in parameter @{p_data_iki} then I get results which I needed.

The problem is that i can get results, but I cannot edit table, because of error in results tab.

--obiee 12c

[Deleted User]

Simple. You didnt supply a default value for your presentation variable: @{pdataiki}{01-01-2016}

Gianni Ceresa

+1 Christian

But you can still edit the table opening it from the bottom-left part of your result tab screen where you have the list of all the views included in your analysis.

ORA-00666

TIMESTAMPDIFF(SQL_TSI_YEAR ,  BIRTH_DATE, @{p_date_till}{01-01-2016})

--On results tab:

State: HY000. Code: 22025. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000

SQL Issued: {call NQSGetQueryColumnInfo('SELECT TIMESTAMPDIFF(SQL_TSI_YEAR , "BIRTH_DATE", 01-01-2016) FROM "Table"')

SQL Issued: SELECT TIMESTAMPDIFF(SQL_TSI_YEAR , "BIRTH_DATE", 01-01-2016) FROM "Table"

[Deleted User]

Why do you not cast it as a timestamp anymore? Come on you almost had this.

ORA-00666

When I want to add a pivot table view I could not do this because of error

ORA-00666

TIMESTAMPDIFF(SQL_TSI_YEAR ,  "Apdraustieji"."Gimimo data", Cast(Cast(@{p_data_iki}{01-01-2016} as Char) as Timestamp))

ORA-01847: day of month must be between 1 and last day of month at OCI call OCIStmtFetch. (HY000)

ORA-00666

I think that error is given because of wrong date format of default parameter value {01-01-2016}. Because when I try  formula with default parameter value {2016-01-01}  (which should be default in my Locale) I get different error:

ORA-01843: not a valid month at OCI call OCIStmtFetch.

[Deleted User]

http://slc02oks.oracle.com:7780/analytics/saw.dll?Answers&path=%2Fshared%2FOTN%2FTimestampDiff

Prodney / Admin123

Alok Surana

have you tried  TIMESTAMPDIFF(SQL_TSI_YEAR ,  BIRTH_DATE, DATE '@{p_data_iki}' )?