Skip to main content
B
bkirsch4-Participant
4-Participant
November 22, 2017
Solved

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

  • November 22, 2017
  • 2 replies
  • 4808 views

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!

Best answer by ankitgupta

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

2 replies

A
ankitgupta1-Visitor
1-Visitor
November 23, 2017

Hi Benjamin Kirsch​,

Have you tried truncating the Timezone and then use

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

L
lguagneli1-Visitor
1-Visitor
November 23, 2017

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);

B
bkirsch4-ParticipantAuthor
4-Participant
November 23, 2017

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?

A
ankitgupta1-VisitorAnswer
1-Visitor
November 24, 2017

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

Terms & ConditionsAccessibility statement