DSXchange

Hi All,
I am extracting data from an oracle database table and loading into other oracle database table.(Using DRS stage).
I have an issue with timestamp field.
While extracting the data from the table the date fields of the table are selected as timestamp.
Ex: To_Char(Field_Date,'YYYY-MM-DD HH24:MI:SS') ..

Most timestamp fields of the target that are loaded are looking good expect for few fields with data..


Source : Field_Date
Value: 07-01-2007 23:00:00

Target: Field_Date
Value: 07-02-2007 00:00:00

The timestamp with 23:00:00 is rounding upto next day. I did use the substrings on the field to get the date & time individually but I am getting the same round up value.

The source stage has generated SQL......

Meena - if you use the same to_char() function to convert the date in SQL (i.e. not using DataStage) what results do you get ?

Make sure the field is a Timestamp in the target stage as well and the stage generates the sql. Then it will do a matching TO_DATE() on your data with the same mask the TO_CHAR() used to select the data: YYYY-MM-DD HH24:MI:SS.

Don't "use substrings on the field to get the date and time individually" unless you really need to. Just pass the data from source Oracle table to the target Oracle table with both ends as Timestamp and don't futz with it in the middle - you'll be fine.

Hi All,
I am running the same SQL outside the DS and I am able see the time in the field data.

Ex: Source : Field_Date
Value: 07-01-2007 23:00:00

And the target field is timestamp too but the field value is getting round up to 07-02-2007 00:00:00..

For testing purpose I designed a new job to load the data into sequential file..
And I see the same round up value to next day.

I tried to do with User define SQL but the same result.....

Timestamp datatype all the way through? Generated sql at both ends?

Hi Craig,
I have generated SQL on both ends with timestamp datatype.

chulett wrote:Timestamp datatype all the way through? Generated sql at both ends? ...

I would suggest 2 things:
1. While using DS 7.5.2 we faced rounding of problem thats was resolved by patch for datastage. So you can first use a peek or temporary file to check what data is going to be loaded.

2. Check with the DBA about the NLS setting of the target.

:-)Sunil

In your favourite sql tool, run the following query on your date field. This is to check if your date is infact corrupted or not. The corrupted dates should show up in your query. If the result of this query is the same as the timestamps which are not showing up correct in your target, then you know what the problem is.

Hi DSguru2B,

I run the query in SQL tool and I am not getting any output.

DSguru2B wrote:In your favourite sql tool, run the following query on your date field.

Code: Select all

        SELECT Field_Date,dump(Field_Date) 
          FROM YourTable
         WHERE Field_Date != Field_Date+0

This is to check if your date is infact corrupted or not. The corrupted dates should show up in your query. If the result of this query is the same as the timestamps which are not showing up correct in your target, then you know what the problem is.

Does it add 1 hour to every source date ?? or are you having problems only with 23:00:00 time value ??

Well, then that rules out the corrupted dates theory. What happens when you select the date as varchar. Do you still see this issue? Is this issue happening only when you pass the sql select from within datastage or even from sql outside of datastage?

Hi Hemant,
This is happening only for 23:00:00.The remaining timestamp fields looks good.

Hemant_Kulkarni wrote:Does it add 1 hour to every source date ?? or are you having problems only with 23:00:00 time value ??

Hi DSguru2B,
I am facing this issue in Datastage,outside the datastage when I run the same SQL then I am able to see the timestamp

Ex: 07-01-2007 23:00:00 (In Sql tool)
07-02-2007 00:00:00 (In DS)
For the same SQL.

I tried, by changing the datatype to Varchar.But no use I am getting the same round up value to next day.

DSguru2B wrote:Well, then that rules out the corrupted dates theory. What happens when you select the date as varchar. Do you still see this issue? Is this issue happening only when you pass the sql select from within datastage or even from sql outside of datastage?

Strange and beats me.
Try providing TRUNC(Field_date) in the derivation column in the column's tab. Do you still get the date with 1 added to it. Or this time you are getting the correct date. Time will be 00:00:00 as you are truncating the time part. This is just for debugging purposes.

I've never heard of nor seen any such thing. A timestamp carried all the way through your job should not be an issue nor 'round up' in any fashion. But then I'm running 7.5.1A not 7.5.2 at the moment.

Rather than continuing to chase the wild gooses, I'd suggest you contact your official support provider and open a ticket with them. Someone has already suggested a patch is available for this - or at least something much like this - why not check with Support and see what they have to say on the issue?