AnsweredAssumed Answered

How can I convert string value into the date or timestamp value in desired format?.

Question asked by fr-ms.usc-gcc.dl on Nov 28, 2017
Latest reply on Nov 28, 2017 by deama10

Hello All,

I am new to CA Service catalog and PAM technology and facing following issue in one of my PAM process.

In CA PAM 12.9.

I want to pass the system date to the INSERT statement using input parameter in the format of dd-MMM-yy hh:mm:ss AM/PM (for e.g. 28-Nov-17 02:01:28 PM).

I am using now() function to get the system date, this gives me the date & time in dd/mm/yyy hh:mm:ss AM/PM format (for e.g. 28/11/2017 02:07:53 PM). I am converting this date into desired format by using formatDate().

for e.g. formatDate(now(),'dd-MMM-yy hh:mm:ss a')

 

The query I am using to insert the values is as follow:

 

INSERT INTO table_name (col1,col2,col3,col4) select '?','?','?','?' from dual where not exists(select 'x' from table_name where col1 = '?')

Here, In my database table 

col1, col2 and col4 are of type NVARCHAR and col3 is of timestamp(6) 

 

My Input Parameters are :

Process.ID

Process.State

formatDate(now(),'dd-MMM-yy hh:mm:ss a')

Process.cust

Process.ID

 

After executing the query it gives me the error 'ORA-01858: a non-numeric character was found where a numeric was expected’

 

I try to modify my Insert statement by using to_timestamp() and to_date() functions of sql.

Following are the modification that I did into my Insert statement:

1.

INSERT INTO table_name (col1,col2,col3,col4) select '?','?',TO_TIMESTAMP('?',DD-MON-YY HH24:MI:SS')','?' from dual where not exists(select 'x' from table_name where col1 = '?')

2. 

INSERT INTO table_name (col1,col2,col3,col4) select '?','?',to_date('?',DD-MON-YY HH24:MI:SS')','?' from dual where not exists(select 'x' from table_name where col1 = '?')

 

But the error still persist.

 

Any help is really appreciated. 

 

 

Outcomes