Oracle DATE to STRING conversion by JDBC Adapter

, ,
1

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

2

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

3

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.

4

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

5

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.