DSXchange

Hello,

I start under PX.

I must extract from the data of a base DB2 with dates and stored in a dataset of which the structure is the same one as the table.

The problem is compared to the dates.
How then I convertr a date db2

my date : 12965 (2003-06-30)
I want : 2003-06-06 or 12965 (2003-06-30)

when I extract the data here is the message which I have :
thank you for your help

Muriel

use
iconv and oconv function

Dinesh

auvray.muriel wrote:Hello,

I start under PX.

I must extract from the data of a base DB2 with dates and stored in a dataset of which the structure is the same one as the table.

The problem is compared to the dates.
How then I convertr a date db2

my date : 12965 (2003-06-30)
I want : 2003-06-06 or 12965 (2003-06-30)

when I extract the data here is the message which I have :

Code: Select all

DB2_F1EWOPFT0,0: Invalid date value in setOutputFieldAsDate() for field '6' in output dataset '0'. 
Date cannot be later than 9999-12-31 or earlier than 0001-01-03.
Julian days value = 1721423.

thank you for your help

Muriel

Is that 'date' internal to AS400?

How do you know the value of 12965 is 2003-06-30? If this is how it is stored in DB2 then you need to get it out of there in the format you want.

I know that on Sybase IQ, we need to import a DATE column as CHAR/VARCHAR otherwise the date comes in completely wrong.

As far as I know, datastage has its own internal number for a date, and thats what sanjay is suggesting.

Once you get the DB2 date into datastage, then start with the iconv/ oconv functions.

From Help:

Iconv("1997 5 27", "D YMD") = 10740
Oconv(10740, "D YMD") = '1997 5 27'

auvray.muriel wrote:Hello,

I start under PX.

I must extract from the data of a base DB2 with dates and stored in a dataset of which the structure is the same one as the table.

The problem is compared to the dates.
How then I convertr a date db2

my date : 12965 (2003-06-30)
I want : 2003-06-06 or 12965 (2003-06-30)

when I extract the data here is the message which I have :

Code: Select all

DB2_F1EWOPFT0,0: Invalid date value in setOutputFieldAsDate() for field '6' in output dataset '0'. 
Date cannot be later than 9999-12-31 or earlier than 0001-01-03.
Julian days value = 1721423.

thank you for your help

Muriel

I had a similar problem when my earliest date was 0001-01-01 I read the date as string and then changed internally and did all my computations.

Anand.

Hi Denzil and Sanjay,

Can we use IConv and OConv in parallel jobs. I don't think so.
What you say may be applicable to server jobs.

Thanks
Rich

In th DB2 database stage change the date datatype to string. From the output of that stage use a modify stage and use the timestamp_from_string or date_from_string (can look into the orchestrate manual) functions to convert to the timestamp/date datatype

Hi Rich

lets put it this way - server jobs yes. Px Im not sure . how do you compare dates in Px then?

Thank you for your answers.

I chose the solution of vikramarjula.

I had another problem.

I had dates 0001-01-01. With the solution of vikramarjula, I can treat the data without problem

Bye

Muriel