Date appearing in internal format when viewed from OCI stage

synsog · Post by **synsog** » Wed Mar 20, 2013 2:02 am

When we try to view data from Datastage OCI stage, the date is getting displayed in some internal format though the datatype is defined as date.
ex. "20-MAR-2013" is displayed as 16516. when the query used in the above OCI stage is executed in toad we are getting the date in proper format like "13-MAR-20" Since we are formatting in the date using the oracle function to_char(DATE_DT,'YY-MON-DD').

chulett · Post by **chulett** » Wed Mar 20, 2013 7:21 am

I remember seeing that behaviour back in the 7.x world. What happens when you run the job, i.e. does it work fine? We solved the issue by always treating an Oracle DATE as a Timestamp in the job, after all that's what it is under the covers.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 20, 2013 1:46 pm

Try setting the Data Element in the Columns grid to "Date".

synsog · Post by **synsog** » Wed Mar 20, 2013 11:16 pm

We tried changing the datatype to Timestamp from date but, when we tried viewing the data it threw an error stating conversion failed. Since, we are using a user defined SQL as given SELECT DW_DATE_ID,TO_CHAR(DATE_DT, 'YY-MON-DD') FROM STG2D_DATE.

We encountered this issue when we were migrating the Jobs to 8.7 from 8.0.1 Data stage Version.When we view the data through the OCI stage in 8.0.1 having the SQL as mentioned above.We are getting the date in internal format which is a number.But when we try to view the same in 8.7 we are getting the date in "DD-MON-YY" format.Because of this mismatch our look up is getting failed.

We need to know is there any settings or any default formats which has to be set in Datastage.

chulett · Post by **chulett** » Wed Mar 20, 2013 11:49 pm

Why user-defined? Why not let the stage generate the SQL? If you did you'd find that it used a better mask for the TO_CHAR() function, one appropriate for a Timestamp data type:

Code: Select all

SELECT DW_DATE_ID,TO_CHAR(DATE_DT, 'YYYY-MM-DD HH24:MI:SS') FROM STG2D_DATE

If you are consistent with your Oracle DATE handling and always use a Timestamp data type for them (yes, even when it "doesn't have" a time component because well, it always does) and never rely on what you think the NLS_DATE format is for the database then you'll be 100% issue free. Which means TO_CHAR() from the source and TO_DATE() for the target with the format string shown above, which the stages will happily generate for you.

My two cents on the subject, for whatever that is worth.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 21, 2013 1:09 am

Try setting the Data Element in the Columns grid to "Date".

In ODBC stages at least this triggers the conversion from internal to external format.

synsog · Post by **synsog** » Sun Mar 24, 2013 12:18 am

Hi,

I have a simple job that read data from oracle table and load into the hash file and I used the following query to read data in OCI stage. The datatype for column DATE_DT is date(19).

"SELECT DW_DATE_ID,TO_CHAR(DATE_DT, 'YYYY-MM-DD HH24:MI:SS') FROM STG2D_DATE"

In Datastage 8.0.1, when I try to view the data from OCI stage, I am getting DATE_DT in internal format and same inserted into the hash file.

But, the same job and same query in Datastage 8.7 giving different result.
when I try to view the data from OCI stage, I am getting DATE_DT in YY-MON-DD format. while loading into hash file it is converting into internal format and the converted value is different as compared to 8.0.1.

example for same DW_DATE_ID '1470', in Datastage 8.0.1 I am getting DATE_DT internal format as 13249 where as in Datastage 8.7 I am getting
15070.

Currently we are working on Datastage Migration from 8.0.1 to 8.7. It's hard to change code from TO_CHAR(DATE_DT, 'YY-MM-DD') to TO_CHAR(DATE_DT, 'YYYY-MM-DD HH24:MI:SS').

Kindly help us, how to resolve the issue without changing the code.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 24, 2013 12:55 pm

Try setting the Data Element in the Columns grid to "Date".