Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff

, ,
1

I am trying to query DB2 database using a parameterized dynamic sql. One of the parameter fields is a timestamp in db2 database and webMethods somehow does not like it. This is the error I am getting
"[ART.114.505] Error while invoking adapter service com.wm.adapter.wmjdbc.services.DynamicSQL. JDBC Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff "

I tried changing to the format (‘yyyy-mm-dd hh:mm:ss.fffffffff’) but no luck. I tried changing the field format type from String to java.util.date and java.sql.date but no luck.

DB2 works with format “yyyy-MM-dd-HH.mm.ss.SSSSSS” and if change the sql to a variable substitution it works but we do not want to go that route.

any suggestions or ideas would be helpful.

2

Can you post the dynamic sql specifics and how you’re calling it?

3

This is how the sql looks like.

Select * from tmp
where id in ?

ID is a timestamp field in tmp table in the db2 database. The ID value comes from a different adapter service call which we are trying to pass it to this call. We get the value in the format (yyyy-MM-dd-HH.mm.ss.SSSSSS) when we use function char on the id so we can use it for subsequent calls. I tried to using this format as well (yyyy-mm-dd hh:mm:ss.fffffffff ) but no luck.

Tried changing the input parameter field type from string to java.sql.date and java.util.date but still ended with errors.

Thx in advance for your help.

4

Shot in the dark. Try:

select * from tmp where id in ‘?’

5

well we would have to use ‘’ for date field and I am using it already. just did not write it in here.

6

What are the types specified for the field on the 2 adapter services that you mention?

7

both are strings…

8

I got this working and wanted to share it. Do not put quotes (‘’) around the date in any format.

Call it this way. This goes with char variables as well. I was assuming earlier to put quotes around char/timestamp fields as we would do in a regular sql.

Select * from tmp
where id in ? with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS Good.

Do not try calling it this way.
Select * from tmp
where id in ? with parameter being ‘yyyy-MM-dd-HH.mm.ss.SSSSSS’ BAD.

or

Select * from tmp
where id in ‘?’ with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS BAD.

1 Like
9

Below query works fine when this is executed at the database but fails through db2 jdbc adapter service.

select sales_order_id from sales_order
where created_date = TIMESTAMP_FORMAT(‘2014-07-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)

But this fails from db2 jdbc CustomSql adapter service with following error:
[SQL0171] Argument 01 of function TIMESTAMP_FORMAT not valid.

I tried with suggested ‘yyyy-MM-dd-…SS.fff…’ format, but it does not work.

10

Joy,

Have you tried this format as suggested GOOD?

Select * from tmp
where id in ? with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS Good.

HTH,
RMG