I really depends on what you are trying to accomplish and the limitations on the source database.
If (and that’s a big if) I’m certain the database holds all values on UTC, I convert to/from java.sql.timestamp (? not sure of the exact class) to the java.date object until the last moment so as not to corrupt the values.
I usually have to resort to my own java services to be able to use the standard Java library which deals with all the intricacies of timezone conversions and datetime calculus.
If I try to simplify our process from the low to high level :
1/ we get the date field from Oracle SELECT (it is the Oracle date format)
2/ get the date field from the webMethods JDBC Adapter
conversion to string or keep date as it is ?
3/ get the date field in my webMethods service to store it in a document webMethods
keep the current date as it is, or convert it with java service in a specific format ?
4/ expose the WSD web service to returns the date field (in dateTime)
maybe the better way is to invariably convert date from the JDBC Adapter with a java library before to store it in our final webMethods document ?
I prefer to keep the date values as Date objects as long as possible and only convert them to string when they have to. This way, the question about the formatting does not even arise (a date can be represented as string in different formats, and different services might expect them in different formats).
Good news if we could keep date format along the whole service.
Here are the steps to try to get date with using ContentType :
In the ContentType editor, I leave as it is :
dateTime {http://… }
If I leave the DATE format in the Adapter JDBC, the date returned is like :
26/06/2018 12:40:14 CEST
(using the output type java.util.Date or java.slq.Date)
To transmit this date to the DateTime field of the webMethods document, how to do pleae ?
the mapping of the DATE field (Jdbc Adapter) to the Document does not give any value (the field is automatically droped)
(if I try with a string format form the JDBC Adapter, the mapping works and the result value is well displayed)
In the adapter service you’ll see the JDBC Type will be DATE or TIMESTAMP (depending on the type of DB and the column definition).
In the Output Field Type, if you specify java.lang.String you’ll get string back in the format defined by the JVM that is running IS. You likely don’t want that (and it can change without your knowledge).
If you specify java.util.Date (avoid java.sql.Date) your service gets back an object, not a string. The DB driver and the JDBC adapter do the work to create a proper Date object. If you’re looking at the variable in the debugger or in results what you’re seeing is a toString() result of that object. The string format used is the default of your JVM. The value represented by this object will never vary so its usually what you want to use for DB queries. As you’ve seen, you cannot map this object to a string field – because it is not a string.
To map the date object to the string field of the document, you call pub.date:formatDate passing the object retrieved via the adapter service and specify this pattern: yyyy-MM-dd’T’HH:mm:ssXXX
Be sure to handle the case where the date object is null (DB column value is null).
Thanks a lot Rob for your reply, and sorry for the delay.
Can you tell me I have to use the inescapable format “yyyy-MM-dd’T’HH:mm:ssXXX”, or I can use any format of my choice to match with the DateTime element below ? (in french, we could use commonly “dd-MM-yyyy HH:mm:ss” but I can keep the english standard)