StringToDate with yyyyMMdd hhmmss format

, ,
1

I want to convert a string into a Date using StringToDate service in IS. The problem is that this service fills in only the year, month and day but not the hour, minute, and second. Does anybody know how to get the Date with a full format or know of another service that does it.

Thanks in advance.

Nour.

2

Nour, I am not sure that there is a stringToDate service in IS. If you are trying to insert a timestamp into a database table, you have several approaches:

  1. Change your schema so that the timestamp column defaults to SYSDATE for each INSERT. UPDATE should have a trigger to change the timestamp, if required.

  2. Use the pub.date:currentDate Built-In Service to generate your date. The Service In variable pattern should read “MM/dd/yyyy HH:mm:ss” which will output “10/04/2002 09:59:41”. You can then use this value as the input to a database service.

Let me know if this helps.

3

Dan,

Thanks for your answer. Yes, there is a wm.enterprise.BuiltInUtil:StringToDate service in IS (with capital S) but when I enter “MM/dd/yyyy HH:mm:ss” in the variable pattern, the service return a date as : Fri Oct 04 00:00:00 EST 2002. I don’t want to use pub.date:currentDate service because it returns a String versus a DATE as output.

4

So you are using the Broker package. I see.

Because the StringToDate receives a string representation of the date in SHORT format and returns a Date object , maybe you can run this in reverse and see what the service expects?

Try running wm.enterprise.BuiltInUtil:DateToString. This service takes a Date object as an input. You can create one using a Java service. If you need help with this, let me know.

By the way, is setting the date field within the Broker environment an option or do you need to get it from within IS?

5

Dan,

I don’t really need to get it from within IS, I have created a custom SQL in EAI using TO_DATE. It converts my string into a date and it seems to be working. I was having a problem inserting null dates but it was fixed by simply checking the Allow Unset Fields box in the properties tab of my component.

Thanks.

6

Excellent. I am glad that you were able to find a solution for your problem.

Take care,
Dan.

7

Hi ,
I have a problem of inserting the current timestamp into the log database (DB2). In short I am mapping a string variable to “CURRENT TIMESTAMP” .I have written a customSQL adapter service which inserts into database " insert into xxx values(?,(select…))"
The first input field is eventtime ( mapped from my string variable)which is of type timestamp in the table. So my input field type is string and my Input JDBC type is timestamp. It works fine for normal values of pattern say ‘yyyy-mm-dd hh:MM:ss.SSSS’. But my requirement is it should work for CURRENT TIMESTAMP. As a last alternative I can use pub.date:currentDate but I need the Database time and not the IS time. Hope someone has come across this situation.
Thanks,
Pavithra.

8

Pavithra,

You can try specifying “sysdate” in your insert statement of customSQL template.

9

Hi RMG ,
I think I should have been a little more clearer. The value that I want to insert is not always the current timestamp. only if it (eventtime) is null then I need to insert the current timestamp.

Thanks,
Pavithra.

10

Pavithra,
In your custom stored procedure you can check for the date which is coming as null.If it is null then on Database you can write some Before Trigger if you want to get the Database Date and before inserting it into table if a particular Date is null then your Before Trigger will take care of putting in the Database Date.Hope this helps.

Thanks,
Puneet Verma

11

Hi Puneet ,
Thanks for your reply. I can’t change the database. I have to do it using adapter services i.e only through webMethods. No Stored Procedures no triggers :slight_smile:

Thanks,
Pavithra.