[Reports] Convert value from string to datetime

[RH_9982091]

Hey there,

I want to create a report to check which objects are expiring in the next 50 days.

I have an attribute for each of my object/WT-Part which contains the expiring-date and also a short discription.

Because of that the attribute is from datatype.string:

Attribute	Value	Dattype
Expiringstate	Expiring 31.12.2021 | Not available anymore	String

Not all WT-Parts have a value for the expiringstate, just those with an expiring-date. 
Now i want to trim the string (just in the reports) that i only have the date.

Attribute	Value	Datatype
Expiringstate	Expiring 31.12.2021 | Not available anymore	String

After that i want to convert that string into dataype.datetime:

Attribute	Value	Datatype
Expiringstate	31.12.2021	Datetime

With that date i want to check which WT-Parts have an expiring-date < 50 days to the current-date.

Is it possible to solve that directly in windchill reports? If yes, how can i do this?

Thanks in advance!!

[aaronjlarson · Accepted solution]

Yes you can do this in Query Builder with database functions. .  To help build the functions I suggest to break it up into smaller pieces in the select tab first to see how they work and then assemble them into larger functions in either select or criteria. 

For example, to check extracting and converting the date create a new entry in the select tab of "Database Function" and first choose "To Date".  Under it directly then add another database function "Substring" as the only argument.  It looks like your string format is consistent so this should work.  The arguments for the substring function should be first the reportable attribute Expiringstate, then 10, then 10 again.  The first 10 indicates the place in the string you'd like to start and the second 10 is how many characters to extract.

Ultimately, to reach your end outcome you'd want to create a series of nested functions in the criteria tab that uses a subtract function that uses the extracted date and the system time which will return you a number of days.   You can set your criteria to be less than 50 on that result to return only the results where items are expiring in less than 50 days.

[Marco_Tosin]

In the Tips & Tricks section of this article, there are some tips on how to use database functions.

More info on this document

[d_graham]

Query Builder has a built in way to pass data to a Java method and then return whatever. In your case the whatever is a Timestamp which is the Expire date.

 While using Query Builder you may have noticed the “Java Method” option.

I’d go this “Java Method” route as once you learn how to use it you can do anything. By anything I mean stuff that simply is not possible using dB functions.

Your case is straight forward. Pass your Java method a String (the value of Expiringstate) then have the method parse the String to get your date in the String format “yyyy-mm-dd 00:00:00”

then just return Timestamp.valueOf(“yyyy-mm-dd 00:00:00”);

BTW you do have to register your Java method so that it’s selectable from the Query Builder but that’s no big deal. PTC has adequate documentation on how to use it.

Hope this helps,

David

[joe_morton]

I'm intrigued by the Java method, but I don't know how to do that. I believe you could do it with nested database functions like previously stated. My biggest tip if you go that route: open your Windchill help and search for "database functions". Each one is listed out with the rules for using it

I would structure a constraint like the following:

• DB Function Time Difference (days)
  • Constant -> use macro -> current date
  • To Date
    • Sub String 
      
      • Report Attribute  -> Expiringstate
      • 10
      • 10

So that should give you the left side of the constraint - how many days until each item expires. Set that to be less than a parameter "days", and then set default "days" = 50. That way, you can easily adjust your report to return items expiring in more or less days.

[Marco_Tosin]

These four articles are about using Java method in query builder:

CS33057 - Using Java interfaces in QueryBuilder reports in Windchill

CS114482 - Using Java Methods in Query Builder in Windchill

CS253057 - When user added java method then selected allow user to decide option all of column user is not able to generate report.

CS303425 - Is it possible to use java method in a query builder data base function in Windchill PDMLink

[RH_9982091]

Thanks to all.

I had not that much time to test it yesterday but made it a few minutes ago and it worked.

@joe_morton your option was also good explained!

@Marco_Tosin thank you for your links and tips!

@d_graham i made it with the db functions. If i have time i will have a look at the java methods!