convertion of date DB2

[auvray.muriel]

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

[sanjay]

use
iconv and oconv function

Dinesh

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

[denzilsyb]

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'

[anandkumarm]

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.

[richdhan]

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

[vikramarjula]

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

[denzilsyb]

Hi Rich

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

[auvray.muriel]

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