nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

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

Find more posts tagged with

Accepted answers

All comments

[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}' )?

Upcoming Event

Analytics - Fusion Data Intelligence (FDI) : Overview for Fusion CX Analytics 26.R3 Application Release
Description: Join our Product Management team to learn about new features that are part of 26.R3 Application release of Fusion Data Intelligence (CX). The session will cover updates on Service, Sales, Marketing & other relevant areas. Presented by: Kausik Misra - Director, CX Product Management, Fusion Data Intelligence…

Top Leaders