DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 6:44 am Reply with quote    Back to top    



Hi,

I have got a problem with respect to a high date eg. 31/12/9999. The high date is being used in our system as expiry date for the active users.

Datastage converts all the high dates, when transferred from a flat file to OCI Plugin, into 31/12/1999. This is causing a lot of problems for us.

I am sure someone in the user group could suggest a solution to this problem.

Thanks.

Niranjan

NOTICE
The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it.

admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 8:36 am Reply with quote    Back to top    

DataStage only does what its told!

Maybe 31/12/9999 is not the correct date format for loading into Oracle - have you tried others (9999-12-31) or using a TO_DATE function or setting your date picture environment variable?

Is 9999-12-31 a legal date for Oracle (he asks, showing limits to his Oracle knowledge)? That is, is the highest date you can enter into Oracle earlier than this date?

-----Original Message-----
From: Niranjan.Purohit@ing.com.au [mailto:Niranjan.Purohit@ing.com.au]
Sent: Wednesday, 16 May 2001 13:45
To: informix-datastage@oliver.com
Subject: Datastage conversion of a year in a date: 9999 to 1999.




Hi,

I have got a problem with respect to a high date eg. 31/12/9999. The high date is being used in our system as expiry date for the active users.

Datastage converts all the high dates, when transferred from a flat file to OCI Plugin, into 31/12/1999. This is causing a lot of problems for us.

I am sure someone in the user group could suggest a solution to this problem.

Thanks.

Niranjan

NOTICE
The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it.


Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 9:15 am Reply with quote    Back to top    

Hate to burst you bubble, Ray, but Oracle can handle 31/12/9999 just fine. (At least Oracle 8.0.4 can - I just tested it.)

To answer the question, I would like to know a little more about the job, such as the source and target data type in the links and the transform expression.

If you source field is text and the target field is timestamp (the correct DataStage type for Oracle date fields), then DataStage is usually pretty good at interpreting the date and formatting it correctly for the timestamp.

If I get a chance, Ill try and set up a quick test.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Wednesday, 16 May 2001 6:37 PM
To: informix-datastage@oliver.com
Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.

DataStage only does what its told!

Maybe 31/12/9999 is not the correct date format for loading into Oracle - have you tried others (9999-12-31) or using a TO_DATE function or setting your date picture environment variable?

Is 9999-12-31 a legal date for Oracle (he asks, showing limits to his Oracle knowledge)? That is, is the highest date you can enter into Oracle earlier than this date?

-----Original Message-----
From: Niranjan.Purohit@ing.com.au [mailto:Niranjan.Purohit@ing.com.au]
Sent: Wednesday, 16 May 2001 13:45
To: informix-datastage@oliver.com
Subject: Datastage conversion of a year in a date: 9999 to 1999.




Hi,

I have got a problem with respect to a high date eg. 31/12/9999. The high date is being used in our system as expiry date for the active users.

Datastage converts all the high dates, when transferred from a flat file to OCI Plugin, into 31/12/1999. This is causing a lot of problems for us.

I am sure someone in the user group could suggest a solution to this problem.

Thanks.

Niranjan

NOTICE
The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it.


Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 9:37 am Reply with quote    Back to top    

Oops, I withdraw my earlier comment about DataStage begin pretty smart about converting dates to timestamp.

If you throw 31/21/9999 at it, it just gets upset. You are going to have to use Iconv and Oconv to convert the date to 9999-12-31 for it to be happy.

This being the case, if you are getting 31/12/1999 in Oracle, I would definitely like to see your transform expression.

David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, 16 May 2001 7:15 PM
To: informix-datastage@oliver.com
Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.

Hate to burst you bubble, Ray, but Oracle can handle 31/12/9999 just fine. (At least Oracle 8.0.4 can - I just tested it.)

To answer the question, I would like to know a little more about the job, such as the source and target data type in the links and the transform expression.

If you source field is text and the target field is timestamp (the correct DataStage type for Oracle date fields), then DataStage is usually pretty good at interpreting the date and formatting it correctly for the timestamp.

If I get a chance, Ill try and set up a quick test.


David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia

e-mail: david@barham.hm

-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Wednesday, 16 May 2001 6:37 PM
To: informix-datastage@oliver.com
Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.

DataStage only does what its told!

Maybe 31/12/9999 is not the correct date format for loading into Oracle - have you tried others (9999-12-31) or using a TO_DATE function or setting your date picture environment variable?

Is 9999-12-31 a legal date for Oracle (he asks, showing limits to his Oracle knowledge)? That is, is the highest date you can enter into Oracle earlier than this date?

-----Original Message-----
From: Niranjan.Purohit@ing.com.au [mailto:Niranjan.Purohit@ing.com.au]
Sent: Wednesday, 16 May 2001 13:45
To: informix-datastage@oliver.com
Subject: Datastage conversion of a year in a date: 9999 to 1999.




Hi,

I have got a problem with respect to a high date eg. 31/12/9999. The high date is being used in our system as expiry date for the active users.

Datastage converts all the high dates, when transferred from a flat file to OCI Plugin, into 31/12/1999. This is causing a lot of problems for us.

I am sure someone in the user group could suggest a solution to this problem.

Thanks.

Niranjan

NOTICE
The information contained in this electronic mail message is privileged and confidential, and is intended only for use of the addressee. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply transmission and delete the message without copying or disclosing it.


Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 10:01 am Reply with quote    Back to top    

Hey Guys,

I have seen almost this same problem. Our High Date was 9/9/9999 but when it got to Oracle it became 9/9/1999 I cant remember the exact details but surfice to say It was Classic Y2K. What was happening was that it converted 9/9/99 to you gessed it 9/9/1999. There was a problem with our use of the TO_DATE function and a setting on Oracle sorry I can not be more clear.

Hope this helps.

Thanks Jack

(Those who know me know where I was on the 9/9/1999 when we found out)
----- Original Message -----
From: "David Barham"
To:
Sent: Wednesday, May 16, 2001 6:45 PM
Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.


> Hate to burst you bubble, Ray, but Oracle can handle 31/12/9999 just
> fine. (At least Oracle 8.0.4 can - I just tested it.)
>
> To answer the question, I would like to know a little more about the
> job, such as the source and target data type in the links and the
> transform expression.
>
> If you source field is text and the target field is timestamp (the
> correct DataStage type for Oracle date fields), then DataStage is
> usually pretty good at interpreting the date and formatting it
> correctly for the
timestamp.
>
> If I get a chance, Ill try and set up a quick test.
>
>
> David Barham
> Information Technology Consultant
> InformAtect Pty Ltd
> Brisbane, Australia
>
> e-mail: david@barham.hm
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Wednesday, 16 May 2001 6:37 PM
> To: informix-datastage@oliver.com
> Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.
>
> DataStage only does what its told!
>
> Maybe 31/12/9999 is not the correct date format for loading into
> Oracle - have you tried others (9999-12-31) or using a TO_DATE
> function or setting your date picture environment variable?
>
> Is 9999-12-31 a legal date for Oracle (he asks, showing limits to his
Oracle
> knowledge)? That is, is the highest date you can enter into Oracle
earlier
> than this date?
>
> -----Original Message-----
> From: Niranjan.Purohit@ing.com.au [mailto:Niranjan.Purohit@ing.com.au]
> Sent: Wednesday, 16 May 2001 13:45
> To: informix-datastage@oliver.com
> Subject: Datastage conversion of a year in a date: 9999 to 1999.
>
>
>
>
> Hi,
>
> I have got a problem with respect to a high date eg. 31/12/9999. The
> high date is being used in our system as expiry date for the active
> users.
>
> Datastage converts all the high dates, when transferred from a flat
> file
to
> OCI
> Plugin, into 31/12/1999. This is causing a lot of problems for us.
>
> I am sure someone in the user group could suggest a solution to this
> problem.
>
> Thanks.
>
> Niranjan
>
> NOTICE
> The information contained in this electronic mail message is
> privileged
and
> confidential, and is intended only for use of the addressee. If you
> are
not
> the
> intended recipient, you are hereby notified that any disclosure,
> reproduction, distribution or other use of this communication is
> strictly prohibited.
If
> you
> have received this communication in error, please notify the sender by
reply
> transmission and delete the message without copying or disclosing it.
>
>
>
>
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed May 16, 2001 4:05 pm Reply with quote    Back to top    

Oracle will store 12/31/9999.

Do you know where the conversion is taking place? One approach may be to treat the date as a string through Data Stage and use the to_date Oracle function in the OCI SQL statement.


--------------------------
Nicholas Galemmo
Information Architect
Nestlé USA

> -----Original Message-----
> From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
> Sent: Wednesday, May 16, 2001 1:37 AM
> To: informix-datastage@oliver.com
> Subject: RE: Datastage conversion of a year in a date: 9999 to 1999.
>
> DataStage only does what its told!
>
> Maybe 31/12/9999 is not the correct date format for loading into
> Oracle - have you tried others (9999-12-31) or using a TO_DATE
> function or setting your date picture environment variable?
>
> Is 9999-12-31 a legal date for Oracle (he asks, showing limits to his
> Oracle knowledge)? That is, is the highest date you can enter into
> Oracle earlier
> than this date?
>
> -----Original Message-----
> From: Niranjan.Purohit@ing.com.au [mailto:Niranjan.Purohit@ing.com.au]
> Sent: Wednesday, 16 May 2001 13:45
> To: informix-datastage@oliver.com
> Subject: Datastage conversion of a year in a date: 9999 to 1999.
>
>
>
>
> Hi,
>
> I have got a problem with respect to a high date eg. 31/12/9999. The
> high date is being used in our system as expiry date for the active
> users.
>
> Datastage converts all the high dates, when transferred from a flat
> file to OCI
> Plugin, into 31/12/1999. This is causing a lot of problems for us.
>
> I am sure someone in the user group could suggest a solution to this
> problem.
>
> Thanks.
>
> Niranjan
>
> NOTICE
> The information contained in this electronic mail message is
> privileged and confidential, and is intended only for use of the
> addressee. If you are not
> the
> intended recipient, you are hereby notified that any disclosure,
> reproduction,
> distribution or other use of this communication is strictly prohibited.
> If
> you
> have received this communication in error, please notify the sender by
> reply
> transmission and delete the message without copying or disclosing it.
>
>
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours