IT Process Automation

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  How can I convert string value into the date or timestamp value in desired format?.

    Posted 11-28-2017 10:11 AM

    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. 

     

     



  • 2.  Re: How can I convert string value into the date or timestamp value in desired format?.
    Best Answer

    Broadcom Employee
    Posted 11-28-2017 11:03 AM

    Date formating is always tricky in PAM.  I found an old case that suggested this:

    var now = new Date().getTime();



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

    Posted 11-28-2017 11:11 AM

    The first thing that I would do is have an insert sql statement that works on sqldeveloper,for example. Than is a matter only to reproduce the same sintax on pam operator. Oracles has a specific format of date that is used on the session, and you should be able to insert it as a string.