Infotable: How to truncate a String and convert to Datetime for a specific colum

[bkirsch]

Hi,

I'm quiete new in the thingworx business and face the following issue:

I fetch an Report from Windchill by an Service and get an resulting infotable. One Field of the columns represents a date/time attribute that unfrotunatley uses an unsupported format like "2017-08-10 11:32:00 CEST". If I use datetime type attribute in the datashape it results in ierror that CEST is malformed. For analysis in thingworx I need this field in datetime format. My approach now is to truncate the "CEST" portion an convert the field from string into datetime. I tried something with derived filed function, but didn't succeed. I can't imagine that this usecase is unusual but haven't found the right answer in the community so far. I looking forward to your assistance. Thanks in advane!

[ankitgupta]

Hi Benjamin Kirsch​,

Have you tried truncating the Timezone and then use

var dt = new date('2017-08-10 11:32:00');

[lguagneli]

Hi Benjamin Kirsch,

I think you can use parseDate,

var dateFormat = "yyyy-MM-dd HH:mm:ss";

// parseDate(stringDate:STRING,dateFormat:STRING):DATETIME

var dateValue = parseDate("yourdatehere", dateFormat);

[bkirsch]

Appriciate your help, but as far as I know those snippets work for a single variable, I need to apply it to each cell of a specific column in an infotable, any idea?

[ankitgupta · Accepted solution]

Benjamin Kirsch​,

Yes, it works for a single variable.

For complete infotable; you will have to add one more column (type dateTime) in your ThingWorx infotable containing data retrieved from Windchill and then traverse through the infotable using for loop and do the conversion and update the Date/Time data in the new column.

Then you can remove the original String DateTime.

You will find the snippet/code for all these in the snippet section.

I hope it helps.

Regards,

Ankit Gupta