Passing timestamp value to SDI flowgraph variable when SP is invoked

[vivek_priyadarshi2]

Hello All,

I am trying to pass two timestamp values (TO and FROM) when invoking the stored procedure that is created on SDI flowgraph execution. Every time i try to execute i am running into an error.

I am trying to pass timestamp values as string LOW = 2022-01-10 15:35:54.040 and HIGH = 2022-01-12 15:35:54.040 and everytime i get a syntax error.

I have tried passing CURRENT_TIMESTAMP and it works but unfortunately the SDI agent is not able to translate this filter into where clause query that SQL server (remote system) can understand. Effectively this leads to SDI pulling all table records and filtering data in memory, leading to high execution time.

I have tried passing the timestamp string as 2022-01-10 15:35:54.040' and '''2022-01-10 15:35:54.040''' but these also end with error shown above

Any suggestion would be helpful.

[vivek_priyadarshi2 · Accepted solution]

I was able to fix this using a workaround. Passing time to and from timestamps as 'YYYYMMDDHH24MISS', in the flowgraph i use TO_TIMESTAMP function to convert the string back to timestamp. The performance is phenominally better with this approach.

CALL "Flowgraph_SP"( '20220110153554','20220112163554');