2 Replies Latest reply: Sep 28, 2012 9:12 AM by 920441 RSS

    String type DateTime value insert into Timestamp type field in a table

    920441
      Hi,


      I am using DB2. i have a table field contains string type of datetime value ("03-06-2009 05:52 PM") and another table contains the another Timestamp type field. while i am trying to insert string type datetime value into Timestamp type field, it returns an error message like,

      " Invalid datetime value in function DateTimeValue. (180,191) CMP_WRK.CMP_VIEW_REPORT.FieldFormula Name:CompareFileObjects PCPC:10631 Statement:212
      Called from:CMP_WRK.CMP_PB.FieldChange Statement:125 "


      i tried various methods like

      &datetimevalue ="03-06-2009 05:52 PM";
      datetimevalue (&datetimevalue );
      or
      timestamp(date(&datetimevalue ),time(&datetimevalue )) in SQL query

      but still i get the same kind of error message.

      any one tell me a solution for this problem..?

      Edited by: sasi on Sep 27, 2012 1:05 AM
        • 1. Re: String type DateTime value insert into Timestamp type field in a table
          HakanBiroglu
          Hi,

          Just tried this on an Oracle env running on PT8.51 and it worked fine ...

          &datetimevalue ="03-06-2009 05:52 PM";
          &datetime = datetimevalue (&datetimevalue);

          Are you sure this gives an error??
          Being on DB2 should not affect PeopleCode functions, only at the moment of writing sql to the database ...
          • 2. Re: String type DateTime value insert into Timestamp type field in a table
            920441
            Hi,

            i am not directly assigned this value to &datetimevalue. Date value dynamically assigned to array(array of any). Then i fetched date value from the array and assigned to &datetimevalue variable.
            i will show you some sample code of my project.


            * Local JavaObject &JavaFileArray = CreateJavaArray("java.lang.Object[]", &JFileList.length);
            &JavaFileArray = &JFileList;
            &FileNames [&JavaFileArray.length] = Null;

            CopyFromJavaArray(&JavaFileArray, &FileNames);

            For &I = 1 To &FileNames.Len
            If Len(RTrim(&FileNames [&I])) > 0 Then

            &DateTimeStamp = " ";
            &FileSize = " ";
            &FileName = " ";

            &FileData = CreateArray("");
            &FileData = Split(&FileNames [&I], "|");

            &DateTimeStamp = &FileData [1];
            &FileSize = LTrim(&FileData [2]);
            &FileName = &FileData [3];
            Break;

            end-if;
            end-for;*