Oracle DATE to STRING conversion by JDBC Adapter

Hello,
I am using JDBC Adapter 6.0.3.8 with FIX14 on wm 6.5. I noticed a problem with JDBC Adapter. I am querying DATE fields from Oracle database. In the dyanmic SQL template I selected the output type for these date fields are strings. I can not select ‘java.util.Date’ as the output format for this field, because this adapter service is also used to query other tables and this implementation can not be changed now. I have observed that I am getting different values at different times.

  1. Few times I get the date value in format yyyy-MM-dd.
  2. Few times I get the date value in format yyyyMMdd.

I checked with my client if he changed the date format in the table. He said ‘no’. What is the fix for it? Anything went wrong here?

Cheers
Gunnasekkhaar

This sounds like a data issue. Use a SQL browser to review the dates on the rows that you are associated with the questionable dates.

Mark

I’m experiencing almost similar problem with dateTimeStamp conversion. I run a stored procedure created using a storedProcedureWithSignature adapter template which returns date time stamp that was created in the Oracle DB hosted in EST time zone. When i receive the output the format will be 08/22/2008 00:00:00 MDT (I’m running this SP in MDT time zone by the way). I checked the outputType and it was java.sql.Date. I changed it to java.lang.String. Then I’m getting only the date but not the time. If i change it to java.util.Date, then I’m getting 8/21/2008 22:00:00 MDT. It seems completely weird to me. Can someone help me in this regard?

BTW, we’re using JDBC Adapter 6.5 on wM IS 6.5.

Hello Mark,
I checked the data in the DB. The format for these date fields is yyyy-MM-dd. As I am framing the query to get the data, I made a work around for this using [B]to_char/b function; Like select to_char(fieldName,‘yyyyMMdd’) from T1. The issue is resolved but still the puzzle remained. How exactly JDBC Adapter do conversions between the specific DB types to strings? Seems to me that I should use a custom/dynamic SQL to get the existing date format and without loss of data (time in this case).

Thanks & Cheers:)
Guna

Good that you solved your problem. I’m trying to find a work around in my case as I’m using a stored procedure. Any ideas on that?

Hope.