Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
auvray.muriel
Participant
Posts: 43 Joined: Wed Feb 19, 2003 7:17 am
Location: France - Paris
Contact:
Post
by auvray.muriel » Tue Jun 22, 2004 8:27 am
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
Premium Member
Posts: 203 Joined: Fri Apr 23, 2004 2:22 am
Post
by sanjay » Wed Jun 23, 2004 1:24 am
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
denzilsyb
Participant
Posts: 186 Joined: Mon Sep 22, 2003 7:38 am
Location: South Africa
Contact:
Post
by denzilsyb » Wed Jun 23, 2004 11:14 am
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'
dnzl
"what the thinker thinks, the prover proves" - Robert Anton Wilson
anandkumarm
Premium Member
Posts: 55 Joined: Tue Feb 24, 2004 8:17 am
Post
by anandkumarm » Wed Jun 23, 2004 1:48 pm
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.
richdhan
Premium Member
Posts: 364 Joined: Thu Feb 12, 2004 12:24 am
Post
by richdhan » Wed Jun 23, 2004 11:24 pm
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
Participant
Posts: 3 Joined: Thu Dec 04, 2003 1:40 pm
Post
by vikramarjula » Wed Jun 23, 2004 11:58 pm
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
Participant
Posts: 186 Joined: Mon Sep 22, 2003 7:38 am
Location: South Africa
Contact:
Post
by denzilsyb » Thu Jun 24, 2004 3:59 am
Hi Rich
lets put it this way - server jobs yes. Px Im not sure
. how do you compare dates in Px then?
dnzl
"what the thinker thinks, the prover proves" - Robert Anton Wilson
auvray.muriel
Participant
Posts: 43 Joined: Wed Feb 19, 2003 7:17 am
Location: France - Paris
Contact:
Post
by auvray.muriel » Fri Jun 25, 2004 1:32 pm
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