Datastage conversion of a year in a date: 9999 to 1999.

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Datastage conversion of a year in a date: 9999 to 1999.

Post by admin »

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
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

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.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

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.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

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.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

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.
>
>
>
>
Locked