Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Timestampdiff giving Invalid identifier error

PragyaPurwar
New Contributor II
New Contributor II

Hi , 

usecase is Identity should be created a day before the start date . 

For this I am using TIMESTAMPDIFF(DAY, STARTDATE, CURRENTDATE) in my where clause . When I am using the same query in data analyzer it is giving me results . But when I am running the job it is giving timestamp invalid identifier . Our source db is Oracle DB

PragyaPurwar_0-1693390888328.png

PragyaPurwar_1-1693391016332.png

 

 

6 REPLIES 6

pmahalle
All-Star
All-Star

Hi @PragyaPurwar ,

Seems like TIMESTAMPDIFF function is not available in Oracle SQL. Can you try with DATEDIFF() function like below and check once.

SELECT DATEDIFF(STARTDATE, CURRENT_DATE())


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

Job is getting stuck using this function 

armaanzahir
Valued Contributor
Valued Contributor

Hi @PragyaPurwar 

Are you trying to check the timestamp difference on the Oracle DB?

You can simply put a minus operator to calculate the difference in timestamp for Oracle DB.

Calculate difference between two timestamps in Oracle 

IGA has MySQL DB which supports TIMESTAMPDIFF function.

 

Regards,
Md Armaan Zahir

My startdate format is Dec 30 , 2023 and currentdate format is 'YYYY-MM-DD' . I tried to use the query select format(startdate , 'YYYY-MM-DD')-Current_date from table_name ; 

PragyaPurwar_0-1693399584797.png

 

@PragyaPurwar Did you tried with DATEDIFF function?


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

Yes DATEIFF is working in data analyzer , when I tried to run the job . It got stuck