I am a new user of this product, so I hope you dont mind me asking this very simple question. I have a string field in my source but this contains a date value, e.g. "1997-01-01", and I want to load this into my target in date data type. What function / transform should I use?
input as char: "1997-01-01"
output in target as date: 1997-01-01
Thanks.
char to date
It really does depend on what your source is.
You say that it is char, so its not coming out of a database as a Date type.
There are lots of Transforms in the SDK for manipulating dates. Take an exploratory browse through these.
The underlying function in pretty much all cases is Iconv() - convert on input - or Oconv() - convert on output.
So you convert your char string to a Date with Iconv(MyDate, "DYMD"), where the first "D" indicates that youre converting a date, and "YMD" indicates that it is in year-month-day order. This may be sufficient to load into a Date data type. Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert it into exactly the required format. Here the first "D" again indicates that youre converting a date, the "-" is the delimiter character, "YMD" indicates year-month-day order and [4,2,2] specifies four digits for the year, two for the month and two for the day, so that month and day will get leading zeroes if necessary.
The two can be combined. For example:
Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 08:27
To: informix-datastage@oliver.com
Subject: char to date
I am a new user of this product, so I hope you dont mind me asking this very simple question. I have a string field in my source but this contains a date value, e.g. "1997-01-01", and I want to load this into my target in date data type. What function / transform should I use?
input as char: "1997-01-01"
output in target as date: 1997-01-01
Thanks.
You say that it is char, so its not coming out of a database as a Date type.
There are lots of Transforms in the SDK for manipulating dates. Take an exploratory browse through these.
The underlying function in pretty much all cases is Iconv() - convert on input - or Oconv() - convert on output.
So you convert your char string to a Date with Iconv(MyDate, "DYMD"), where the first "D" indicates that youre converting a date, and "YMD" indicates that it is in year-month-day order. This may be sufficient to load into a Date data type. Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert it into exactly the required format. Here the first "D" again indicates that youre converting a date, the "-" is the delimiter character, "YMD" indicates year-month-day order and [4,2,2] specifies four digits for the year, two for the month and two for the day, so that month and day will get leading zeroes if necessary.
The two can be combined. For example:
Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 08:27
To: informix-datastage@oliver.com
Subject: char to date
I am a new user of this product, so I hope you dont mind me asking this very simple question. I have a string field in my source but this contains a date value, e.g. "1997-01-01", and I want to load this into my target in date data type. What function / transform should I use?
input as char: "1997-01-01"
output in target as date: 1997-01-01
Thanks.
What type of stage is your output? If it something like ODBC (I hope Im right, cant remember the last time I did this) or ORAOCI8, you output date should have a type in DataStage of "TimeStamp". If this is the case, the expression may be as simple as
SourceDate:" 00:00:00"
This will depend on what your output stage is though. I think with SQL Server (via ODBC) there are some fractions of a second, eg:
SourceDate:" 00:00:00.000"
I could have the number of digits wrong though.
Hopefully this will point you in the right direction.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 10:27
To: informix-datastage@oliver.com
Subject: char to date
I am a new user of this product, so I hope you dont mind me asking this very simple question. I have a string field in my source but this contains a date value, e.g. "1997-01-01", and I want to load this into my target in date data type. What function / transform should I use?
input as char: "1997-01-01"
output in target as date: 1997-01-01
Thanks.
SourceDate:" 00:00:00"
This will depend on what your output stage is though. I think with SQL Server (via ODBC) there are some fractions of a second, eg:
SourceDate:" 00:00:00.000"
I could have the number of digits wrong though.
Hopefully this will point you in the right direction.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 10:27
To: informix-datastage@oliver.com
Subject: char to date
I am a new user of this product, so I hope you dont mind me asking this very simple question. I have a string field in my source but this contains a date value, e.g. "1997-01-01", and I want to load this into my target in date data type. What function / transform should I use?
input as char: "1997-01-01"
output in target as date: 1997-01-01
Thanks.
Thanks for your replies. I just want to make it clearer.
My ODBC data source (Progress DB) is having a DATE field type. I loaded this table definition into DataStage (Length = 10 Display = 10). My target is Oracle8 which already contains my target table. I defined the corresponding field as DATE data type. I loaded this table definition into DataStage and this field was recognized as TIMESTAMP (Length = 38 Display = 20).
When I run my job, I have this warning message:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START"
Value treated as NULL
Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
Please advise. Thanks.
Rochelyn
*******
>It really does depend on what your source is.
>You say that it is char, so its not coming out of a database as a Date
>type.
>
>There are lots of Transforms in the SDK for manipulating dates. Take
>an exploratory browse through these.
>
>The underlying function in pretty much all cases is Iconv() - convert
>on input - or Oconv() - convert on output.
>
>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>where
>the first "D" indicates that youre converting a date, and "YMD"
>indicates
>that it is in year-month-day order. This may be sufficient to load
>into a
>Date data type.
>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>it into exactly the required format. Here the first "D" again
>indicates that
>youre converting a date, the "-" is the delimiter character, "YMD"
>indicates year-month-day order and [4,2,2] specifies four digits for
>the year, two for the month and two for the day, so that month and day
>will get
>leading zeroes if necessary.
>
>The two can be combined. For example:
>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>
>
>-----Original Message-----
>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>Sent: Thursday, 29 March 2001 08:27
>To: informix-datastage@oliver.com
>Subject: char to date
>
>
>I am a new user of this product, so I hope you dont mind me asking
>this very simple question. I have a string field in my source but this
>contains
>a date value, e.g. "1997-01-01", and I want to load this into my target
>in
>date data type. What function / transform should I use?
>
>input as char: "1997-01-01"
>output in target as date: 1997-01-01
>
>Thanks.
>
>
My ODBC data source (Progress DB) is having a DATE field type. I loaded this table definition into DataStage (Length = 10 Display = 10). My target is Oracle8 which already contains my target table. I defined the corresponding field as DATE data type. I loaded this table definition into DataStage and this field was recognized as TIMESTAMP (Length = 38 Display = 20).
When I run my job, I have this warning message:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START"
Value treated as NULL
Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
Please advise. Thanks.
Rochelyn
*******
>It really does depend on what your source is.
>You say that it is char, so its not coming out of a database as a Date
>type.
>
>There are lots of Transforms in the SDK for manipulating dates. Take
>an exploratory browse through these.
>
>The underlying function in pretty much all cases is Iconv() - convert
>on input - or Oconv() - convert on output.
>
>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>where
>the first "D" indicates that youre converting a date, and "YMD"
>indicates
>that it is in year-month-day order. This may be sufficient to load
>into a
>Date data type.
>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>it into exactly the required format. Here the first "D" again
>indicates that
>youre converting a date, the "-" is the delimiter character, "YMD"
>indicates year-month-day order and [4,2,2] specifies four digits for
>the year, two for the month and two for the day, so that month and day
>will get
>leading zeroes if necessary.
>
>The two can be combined. For example:
>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>
>
>-----Original Message-----
>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>Sent: Thursday, 29 March 2001 08:27
>To: informix-datastage@oliver.com
>Subject: char to date
>
>
>I am a new user of this product, so I hope you dont mind me asking
>this very simple question. I have a string field in my source but this
>contains
>a date value, e.g. "1997-01-01", and I want to load this into my target
>in
>date data type. What function / transform should I use?
>
>input as char: "1997-01-01"
>output in target as date: 1997-01-01
>
>Thanks.
>
>
When youre working with databases you (and therefore DataStage) must follow their rules. You need to provide a TIMESTAMP to Oracle, either by generating the timestamp in DataStage or by a date function in a user-defined Oracle INSERT statement.
Look at the TIMESTAMP transform for one example of how you might accomplish the former. There are other examples in the SDK, but these assume a different generic timestamp format.
Be careful too that what youre getting from Progress may really be a date (that is, a computer-format date rather than a human-readable date) since its data type is DATE. You can use the DataStage debugger to see what youre actually getting. If its a computer-format date, you do not need the
Iconv() function.
-----Original Message-----
From: rochelyn@summit.com.sg@netnet.com.sg
[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
Sent: Thursday, 29 March 2001 10:45
To: informix-datastage@oliver.com
Subject: RE: char to date
Thanks for your replies. I just want to make it clearer.
My ODBC data source (Progress DB) is having a DATE field type. I loaded this table definition into DataStage (Length = 10 Display = 10). My target is Oracle8 which already contains my target table. I defined the corresponding field as DATE data type. I loaded this table definition into DataStage and this field was recognized as TIMESTAMP (Length = 38 Display = 20).
When I run my job, I have this warning message:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START"
Value treated as NULL
Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
Please advise. Thanks.
Rochelyn
*******
>It really does depend on what your source is.
>You say that it is char, so its not coming out of a database as a Date
>type.
>
>There are lots of Transforms in the SDK for manipulating dates. Take
>an exploratory browse through these.
>
>The underlying function in pretty much all cases is Iconv() - convert
>on input - or Oconv() - convert on output.
>
>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>where
>the first "D" indicates that youre converting a date, and "YMD"
>indicates
>that it is in year-month-day order. This may be sufficient to load
>into a
>Date data type.
>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>it into exactly the required format. Here the first "D" again
>indicates that
>youre converting a date, the "-" is the delimiter character, "YMD"
>indicates year-month-day order and [4,2,2] specifies four digits for
>the year, two for the month and two for the day, so that month and day
>will get
>leading zeroes if necessary.
>
>The two can be combined. For example:
>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>
>
>-----Original Message-----
>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>Sent: Thursday, 29 March 2001 08:27
>To: informix-datastage@oliver.com
>Subject: char to date
>
>
>I am a new user of this product, so I hope you dont mind me asking
>this very simple question. I have a string field in my source but this
>contains
>a date value, e.g. "1997-01-01", and I want to load this into my target
>in
>date data type. What function / transform should I use?
>
>input as char: "1997-01-01"
>output in target as date: 1997-01-01
>
>Thanks.
>
>
Look at the TIMESTAMP transform for one example of how you might accomplish the former. There are other examples in the SDK, but these assume a different generic timestamp format.
Be careful too that what youre getting from Progress may really be a date (that is, a computer-format date rather than a human-readable date) since its data type is DATE. You can use the DataStage debugger to see what youre actually getting. If its a computer-format date, you do not need the
Iconv() function.
-----Original Message-----
From: rochelyn@summit.com.sg@netnet.com.sg
[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
Sent: Thursday, 29 March 2001 10:45
To: informix-datastage@oliver.com
Subject: RE: char to date
Thanks for your replies. I just want to make it clearer.
My ODBC data source (Progress DB) is having a DATE field type. I loaded this table definition into DataStage (Length = 10 Display = 10). My target is Oracle8 which already contains my target table. I defined the corresponding field as DATE data type. I loaded this table definition into DataStage and this field was recognized as TIMESTAMP (Length = 38 Display = 20).
When I run my job, I have this warning message:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START"
Value treated as NULL
Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
Please advise. Thanks.
Rochelyn
*******
>It really does depend on what your source is.
>You say that it is char, so its not coming out of a database as a Date
>type.
>
>There are lots of Transforms in the SDK for manipulating dates. Take
>an exploratory browse through these.
>
>The underlying function in pretty much all cases is Iconv() - convert
>on input - or Oconv() - convert on output.
>
>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>where
>the first "D" indicates that youre converting a date, and "YMD"
>indicates
>that it is in year-month-day order. This may be sufficient to load
>into a
>Date data type.
>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>it into exactly the required format. Here the first "D" again
>indicates that
>youre converting a date, the "-" is the delimiter character, "YMD"
>indicates year-month-day order and [4,2,2] specifies four digits for
>the year, two for the month and two for the day, so that month and day
>will get
>leading zeroes if necessary.
>
>The two can be combined. For example:
>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>
>
>-----Original Message-----
>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>Sent: Thursday, 29 March 2001 08:27
>To: informix-datastage@oliver.com
>Subject: char to date
>
>
>I am a new user of this product, so I hope you dont mind me asking
>this very simple question. I have a string field in my source but this
>contains
>a date value, e.g. "1997-01-01", and I want to load this into my target
>in
>date data type. What function / transform should I use?
>
>input as char: "1997-01-01"
>output in target as date: 1997-01-01
>
>Thanks.
>
>
My source date field is in YYYY-DD-MM format. I want to load this date field into my oracle table. I have an Oracle table to store this info and the field is defined as DATE data type. When I run my job, it gave the error message as stated below.
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
When I checked my output table, the date column was null. I tried to change the data type in my Oracle table to VarChar, it went through. But, this is not what I want, I want my output field to be a DATE field as I have originally defined in my table.
What I mean by TIMESTAMP in my email below is, thats the TYPE that was recognized by DataStage for my DATE (Orderdate) field when I loaded the table definition into Manager. I wasnt trying to create a timestamp. I hope I understood correctly your point.
Thanks.
*******
>When youre working with databases you (and therefore DataStage) must
>follow
>their rules. You need to provide a TIMESTAMP to Oracle, either by
>generating the timestamp in DataStage or by a date function in a
>user-defined Oracle INSERT statement.
>
>Look at the TIMESTAMP transform for one example of how you might
>accomplish
>the former. There are other examples in the SDK, but these assume a
>different generic timestamp format.
>
>Be careful too that what youre getting from Progress may really be a
>date
>(that is, a computer-format date rather than a human-readable date)
>since its data type is DATE. You can use the DataStage debugger to see
>what youre actually getting. If its a computer-format date, you do
>not need the
>Iconv() function.
>
>-----Original Message-----
>From: rochelyn@summit.com.sg@netnet.com.sg
>[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
>Sent: Thursday, 29 March 2001 10:45
>To: informix-datastage@oliver.com
>Subject: RE: char to date
>
>
>Thanks for your replies. I just want to make it clearer.
>
>My ODBC data source (Progress DB) is having a DATE field type. I
>loaded this table definition into DataStage (Length = 10 Display = 10).
>My target is Oracle8
>which already contains my target table. I defined the corresponding field
>as
>DATE data type. I loaded this table definition into DataStage and this
>field was recognized as TIMESTAMP (Length = 38 Display = 20).
>
>When I run my job, I have this warning message:
>PC2.pc_mstr_EFS: At row 1, link "output", while processing column
>"PC_START"
>
>Value treated as NULL
>Attempt to convert String value "2099-22-09" to Timestamp type
>unsuccessful.
>
>
>Please advise. Thanks.
>
>Rochelyn
>
>*******
>
>>It really does depend on what your source is.
>>You say that it is char, so its not coming out of a database as a
>>Date type.
>>
>>There are lots of Transforms in the SDK for manipulating dates. Take
>>an exploratory browse through these.
>>
>>The underlying function in pretty much all cases is Iconv() - convert
>>on input - or Oconv() - convert on output.
>>
>>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>>where
>
>>the first "D" indicates that youre converting a date, and "YMD"
>>indicates
>
>>that it is in year-month-day order. This may be sufficient to load
>>into a
>
>>Date data type.
>>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>>it into exactly the required format. Here the first "D" again
>>indicates that
>
>>youre converting a date, the "-" is the delimiter character, "YMD"
>>indicates year-month-day order and [4,2,2] specifies four digits for
>>the year, two for the month and two for the day, so that month and day
>>will get
>
>>leading zeroes if necessary.
>>
>>The two can be combined. For example:
>>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>>
>>
>>-----Original Message-----
>>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>>Sent: Thursday, 29 March 2001 08:27
>>To: informix-datastage@oliver.com
>>Subject: char to date
>>
>>
>>I am a new user of this product, so I hope you dont mind me asking
>>this very simple question. I have a string field in my source but
>>this contains
>
>>a date value, e.g. "1997-01-01", and I want to load this into my
>>target in
>
>>date data type. What function / transform should I use?
>>
>>input as char: "1997-01-01"
>>output in target as date: 1997-01-01
>>
>>Thanks.
>>
>>
>
>
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
When I checked my output table, the date column was null. I tried to change the data type in my Oracle table to VarChar, it went through. But, this is not what I want, I want my output field to be a DATE field as I have originally defined in my table.
What I mean by TIMESTAMP in my email below is, thats the TYPE that was recognized by DataStage for my DATE (Orderdate) field when I loaded the table definition into Manager. I wasnt trying to create a timestamp. I hope I understood correctly your point.
Thanks.
*******
>When youre working with databases you (and therefore DataStage) must
>follow
>their rules. You need to provide a TIMESTAMP to Oracle, either by
>generating the timestamp in DataStage or by a date function in a
>user-defined Oracle INSERT statement.
>
>Look at the TIMESTAMP transform for one example of how you might
>accomplish
>the former. There are other examples in the SDK, but these assume a
>different generic timestamp format.
>
>Be careful too that what youre getting from Progress may really be a
>date
>(that is, a computer-format date rather than a human-readable date)
>since its data type is DATE. You can use the DataStage debugger to see
>what youre actually getting. If its a computer-format date, you do
>not need the
>Iconv() function.
>
>-----Original Message-----
>From: rochelyn@summit.com.sg@netnet.com.sg
>[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
>Sent: Thursday, 29 March 2001 10:45
>To: informix-datastage@oliver.com
>Subject: RE: char to date
>
>
>Thanks for your replies. I just want to make it clearer.
>
>My ODBC data source (Progress DB) is having a DATE field type. I
>loaded this table definition into DataStage (Length = 10 Display = 10).
>My target is Oracle8
>which already contains my target table. I defined the corresponding field
>as
>DATE data type. I loaded this table definition into DataStage and this
>field was recognized as TIMESTAMP (Length = 38 Display = 20).
>
>When I run my job, I have this warning message:
>PC2.pc_mstr_EFS: At row 1, link "output", while processing column
>"PC_START"
>
>Value treated as NULL
>Attempt to convert String value "2099-22-09" to Timestamp type
>unsuccessful.
>
>
>Please advise. Thanks.
>
>Rochelyn
>
>*******
>
>>It really does depend on what your source is.
>>You say that it is char, so its not coming out of a database as a
>>Date type.
>>
>>There are lots of Transforms in the SDK for manipulating dates. Take
>>an exploratory browse through these.
>>
>>The underlying function in pretty much all cases is Iconv() - convert
>>on input - or Oconv() - convert on output.
>>
>>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
>>where
>
>>the first "D" indicates that youre converting a date, and "YMD"
>>indicates
>
>>that it is in year-month-day order. This may be sufficient to load
>>into a
>
>>Date data type.
>>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>>it into exactly the required format. Here the first "D" again
>>indicates that
>
>>youre converting a date, the "-" is the delimiter character, "YMD"
>>indicates year-month-day order and [4,2,2] specifies four digits for
>>the year, two for the month and two for the day, so that month and day
>>will get
>
>>leading zeroes if necessary.
>>
>>The two can be combined. For example:
>>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>>
>>
>>-----Original Message-----
>>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>>Sent: Thursday, 29 March 2001 08:27
>>To: informix-datastage@oliver.com
>>Subject: char to date
>>
>>
>>I am a new user of this product, so I hope you dont mind me asking
>>this very simple question. I have a string field in my source but
>>this contains
>
>>a date value, e.g. "1997-01-01", and I want to load this into my
>>target in
>
>>date data type. What function / transform should I use?
>>
>>input as char: "1997-01-01"
>>output in target as date: 1997-01-01
>>
>>Thanks.
>>
>>
>
>
Your problem could be due to using User-Define SQL in your OCI stage. If this is the case, youd need to explicitly convert your date using Oracless to_date(). Below is one of David Barhams reply to what I think is your problem. Also, this is mentioned in the OCI Stage manual (oraoci8.pdf).
HTH
Hong
When using the ORAOCI8 stage, use a data type of TIMESTAMP for ALL date fields.
If you are using generated queries (which hopefully is most of the time), Then there is nothing more for you to do.
However, if you are using a user defined query, there is one more thing you need to know. When reading from or writing to Oracle, treat the "timestamp" field as a character field. That is, when writing to Oracle, you need a construct like, for example, to_date(:3,YYYY-MM-DD HH24:MI:SS). :3 refers to the 3rd field in the link (in this case). When reading from Oracle, you will need to_char(date_field, YYYY-MM-DD HH24:MI:SS).
On a reference link, with a user defined query, you may need both, especially if one of your keys is a date. Also remember that on a reference link, :1, :2, :3 etc refers to the key fields only.
> -----Original Message-----
> From: rochelyn [SMTP:rochelyn@summit.com.sg]
> Sent: Thursday, 29 March 2001 16:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
> message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
> change the data type in my Oracle table to VarChar, it went through.
> But, this is
> not what I want, I want my output field to be a DATE field as I have
> originally
> defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
> accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
> date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
> field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
> indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
> a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
> that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
> in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
HTH
Hong
When using the ORAOCI8 stage, use a data type of TIMESTAMP for ALL date fields.
If you are using generated queries (which hopefully is most of the time), Then there is nothing more for you to do.
However, if you are using a user defined query, there is one more thing you need to know. When reading from or writing to Oracle, treat the "timestamp" field as a character field. That is, when writing to Oracle, you need a construct like, for example, to_date(:3,YYYY-MM-DD HH24:MI:SS). :3 refers to the 3rd field in the link (in this case). When reading from Oracle, you will need to_char(date_field, YYYY-MM-DD HH24:MI:SS).
On a reference link, with a user defined query, you may need both, especially if one of your keys is a date. Also remember that on a reference link, :1, :2, :3 etc refers to the key fields only.
> -----Original Message-----
> From: rochelyn [SMTP:rochelyn@summit.com.sg]
> Sent: Thursday, 29 March 2001 16:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
> message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
> change the data type in my Oracle table to VarChar, it went through.
> But, this is
> not what I want, I want my output field to be a DATE field as I have
> originally
> defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
> accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
> date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
> field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
> indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
> a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
> that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
> in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
The TimeStamp type in DataStage is basically a string in a very specific format.
To get a date out to Oracle you definitely need to use TimeStamp in DataStage and date on the Oracle table definition. The metadata you describe was generated correctly. Remember that Oracle dates hold date and time.
TimeStamps need to have the date part in YYYY-MM-DD format, not the YYYY-DD-MM format that you have.
Use the Iconv and Oconv functions that Ray described to move the date to the timestamp column and that should solve your problem.
Your expression where you move the date into the timestamp should be something like (to slightly adjust Rays example to suit your situation)
Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
I am not sure what standard functions there are to do this. Failing anything else, I suggest that you create a transform for the above expression.
-----Original Message-----
From: rochelyn@summit.com.sg@netnet.com.sg
[mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
Sent: Thursday, 29 March 2001 14:23
To: informix-datastage@oliver.com
Subject: RE: char to date
My source date field is in YYYY-DD-MM format. I want to load this date field into my oracle table. I have an Oracle table to store this info and the field is defined as DATE data type. When I run my job, it gave the error message as stated below.
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
When I checked my output table, the date column was null. I tried to change the data type in my Oracle table to VarChar, it went through. But, this is not what I want, I want my output field to be a DATE field as I have originally defined in my table.
What I mean by TIMESTAMP in my email below is, thats the TYPE that was recognized by DataStage for my DATE (Orderdate) field when I loaded the table definition into Manager. I wasnt trying to create a timestamp. I hope I understood correctly your point.
Thanks.
*******
>When youre working with databases you (and therefore DataStage) must
follow
>their rules. You need to provide a TIMESTAMP to Oracle, either by
>generating the timestamp in DataStage or by a date function in a
>user-defined Oracle INSERT statement.
>
>Look at the TIMESTAMP transform for one example of how you might
>accomplish
>the former. There are other examples in the SDK, but these assume a
>different generic timestamp format.
>
>Be careful too that what youre getting from Progress may really be a
>date
>(that is, a computer-format date rather than a human-readable date)
>since its data type is DATE. You can use the DataStage debugger to see
>what youre actually getting. If its a computer-format date, you do
>not need
the
>Iconv() function.
>
>-----Original Message-----
>From: rochelyn@summit.com.sg@netnet.com.sg
>[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
>Sent: Thursday, 29 March 2001 10:45
>To: informix-datastage@oliver.com
>Subject: RE: char to date
>
>
>Thanks for your replies. I just want to make it clearer.
>
>My ODBC data source (Progress DB) is having a DATE field type. I
>loaded this table definition into DataStage (Length = 10 Display = 10).
>My target is Oracle8
>which already contains my target table. I defined the corresponding field
>as
>DATE data type. I loaded this table definition into DataStage and this
>field was recognized as TIMESTAMP (Length = 38 Display = 20).
>
>When I run my job, I have this warning message:
>PC2.pc_mstr_EFS: At row 1, link "output", while processing column
"PC_START"
>
>Value treated as NULL
>Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
>Please advise. Thanks.
>
>Rochelyn
>
>*******
>
>>It really does depend on what your source is.
>>You say that it is char, so its not coming out of a database as a
>>Date type.
>>
>>There are lots of Transforms in the SDK for manipulating dates. Take
>>an exploratory browse through these.
>>
>>The underlying function in pretty much all cases is Iconv() - convert
>>on input - or Oconv() - convert on output.
>>
>>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
where
>
>>the first "D" indicates that youre converting a date, and "YMD"
>>indicates
>
>>that it is in year-month-day order. This may be sufficient to load
>>into a
>
>>Date data type.
>>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>>it into exactly the required format. Here the first "D" again
>>indicates that
>
>>youre converting a date, the "-" is the delimiter character, "YMD"
>>indicates year-month-day order and [4,2,2] specifies four digits for
>>the year, two for the month and two for the day, so that month and day
>>will
get
>
>>leading zeroes if necessary.
>>
>>The two can be combined. For example:
>>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>>
>>
>>-----Original Message-----
>>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>>Sent: Thursday, 29 March 2001 08:27
>>To: informix-datastage@oliver.com
>>Subject: char to date
>>
>>
>>I am a new user of this product, so I hope you dont mind me asking
>>this very simple question. I have a string field in my source but
>>this
contains
>
>>a date value, e.g. "1997-01-01", and I want to load this into my
>>target in
>
>>date data type. What function / transform should I use?
>>
>>input as char: "1997-01-01"
>>output in target as date: 1997-01-01
>>
>>Thanks.
>>
>>
>
>
To get a date out to Oracle you definitely need to use TimeStamp in DataStage and date on the Oracle table definition. The metadata you describe was generated correctly. Remember that Oracle dates hold date and time.
TimeStamps need to have the date part in YYYY-MM-DD format, not the YYYY-DD-MM format that you have.
Use the Iconv and Oconv functions that Ray described to move the date to the timestamp column and that should solve your problem.
Your expression where you move the date into the timestamp should be something like (to slightly adjust Rays example to suit your situation)
Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
I am not sure what standard functions there are to do this. Failing anything else, I suggest that you create a transform for the above expression.
-----Original Message-----
From: rochelyn@summit.com.sg@netnet.com.sg
[mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
Sent: Thursday, 29 March 2001 14:23
To: informix-datastage@oliver.com
Subject: RE: char to date
My source date field is in YYYY-DD-MM format. I want to load this date field into my oracle table. I have an Oracle table to store this info and the field is defined as DATE data type. When I run my job, it gave the error message as stated below.
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
When I checked my output table, the date column was null. I tried to change the data type in my Oracle table to VarChar, it went through. But, this is not what I want, I want my output field to be a DATE field as I have originally defined in my table.
What I mean by TIMESTAMP in my email below is, thats the TYPE that was recognized by DataStage for my DATE (Orderdate) field when I loaded the table definition into Manager. I wasnt trying to create a timestamp. I hope I understood correctly your point.
Thanks.
*******
>When youre working with databases you (and therefore DataStage) must
follow
>their rules. You need to provide a TIMESTAMP to Oracle, either by
>generating the timestamp in DataStage or by a date function in a
>user-defined Oracle INSERT statement.
>
>Look at the TIMESTAMP transform for one example of how you might
>accomplish
>the former. There are other examples in the SDK, but these assume a
>different generic timestamp format.
>
>Be careful too that what youre getting from Progress may really be a
>date
>(that is, a computer-format date rather than a human-readable date)
>since its data type is DATE. You can use the DataStage debugger to see
>what youre actually getting. If its a computer-format date, you do
>not need
the
>Iconv() function.
>
>-----Original Message-----
>From: rochelyn@summit.com.sg@netnet.com.sg
>[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
>Sent: Thursday, 29 March 2001 10:45
>To: informix-datastage@oliver.com
>Subject: RE: char to date
>
>
>Thanks for your replies. I just want to make it clearer.
>
>My ODBC data source (Progress DB) is having a DATE field type. I
>loaded this table definition into DataStage (Length = 10 Display = 10).
>My target is Oracle8
>which already contains my target table. I defined the corresponding field
>as
>DATE data type. I loaded this table definition into DataStage and this
>field was recognized as TIMESTAMP (Length = 38 Display = 20).
>
>When I run my job, I have this warning message:
>PC2.pc_mstr_EFS: At row 1, link "output", while processing column
"PC_START"
>
>Value treated as NULL
>Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
>Please advise. Thanks.
>
>Rochelyn
>
>*******
>
>>It really does depend on what your source is.
>>You say that it is char, so its not coming out of a database as a
>>Date type.
>>
>>There are lots of Transforms in the SDK for manipulating dates. Take
>>an exploratory browse through these.
>>
>>The underlying function in pretty much all cases is Iconv() - convert
>>on input - or Oconv() - convert on output.
>>
>>So you convert your char string to a Date with Iconv(MyDate, "DYMD"),
where
>
>>the first "D" indicates that youre converting a date, and "YMD"
>>indicates
>
>>that it is in year-month-day order. This may be sufficient to load
>>into a
>
>>Date data type.
>>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to convert
>>it into exactly the required format. Here the first "D" again
>>indicates that
>
>>youre converting a date, the "-" is the delimiter character, "YMD"
>>indicates year-month-day order and [4,2,2] specifies four digits for
>>the year, two for the month and two for the day, so that month and day
>>will
get
>
>>leading zeroes if necessary.
>>
>>The two can be combined. For example:
>>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
>>
>>
>>-----Original Message-----
>>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
>>Sent: Thursday, 29 March 2001 08:27
>>To: informix-datastage@oliver.com
>>Subject: char to date
>>
>>
>>I am a new user of this product, so I hope you dont mind me asking
>>this very simple question. I have a string field in my source but
>>this
contains
>
>>a date value, e.g. "1997-01-01", and I want to load this into my
>>target in
>
>>date data type. What function / transform should I use?
>>
>>input as char: "1997-01-01"
>>output in target as date: 1997-01-01
>>
>>Thanks.
>>
>>
>
>
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
Rochelyn,
The answer to your question about viewing the data in DataStage is to change the column back to a timestamp.
The DataStage timestamp is the "correct" way to handle Oracle dates.
To refer back to your error:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
DataStage would fail to convert this string to timestamp because of the day and month ordering.
If your input column is input.MyDate, and it really is a string type with the value shown above, then the output field should be of type timestamp as you described earlier and the expression should be
Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
Im pretty sure this should work (not able to test it myself just at the moment). If not, try:
Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
Sorry about not being sure ... I never have to write this stuff any more as we have routines which do all this now.
If neither of these work, please send us the exact error message.
Good luck,
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 22:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
The answer to your question about viewing the data in DataStage is to change the column back to a timestamp.
The DataStage timestamp is the "correct" way to handle Oracle dates.
To refer back to your error:
PC2.pc_mstr_EFS: At row 1, link "output", while processing column "PC_START". Value treated as NULL Attempt to convert String value "2099-22-09" to Timestamp type unsuccessful.
DataStage would fail to convert this string to timestamp because of the day and month ordering.
If your input column is input.MyDate, and it really is a string type with the value shown above, then the output field should be of type timestamp as you described earlier and the expression should be
Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
Im pretty sure this should work (not able to test it myself just at the moment). If not, try:
Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
Sorry about not being sure ... I never have to write this stuff any more as we have routines which do all this now.
If neither of these work, please send us the exact error message.
Good luck,
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 22:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
David,
Thanks for your reply.
I tried using: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]") without changing the data type to Date in DataStage but still failed. I also tried just: Iconv(input.MyDate, "DYDM"), still unsuccessful. Then, I decided to maintain this conversion and changed the datatype to Date instead of Timestamp, and I was able to load without errors into my target, but with the problem in "View Data" in DStage as mentioned.
Thank you for this new suggestion: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00". Ill try this out and will let you know.
Cheers!
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 11:52 PM
Subject: RE: char to date
> Rochelyn,
>
> The answer to your question about viewing the data in DataStage is to
change
> the column back to a timestamp.
>
> The DataStage timestamp is the "correct" way to handle Oracle dates.
>
> To refer back to your error:
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
> DataStage would fail to convert this string to timestamp because of
> the
day
> and month ordering.
>
> If your input column is input.MyDate, and it really is a string type
> with the value shown above, then the output field should be of type
> timestamp
as
> you described earlier and the expression should be
>
> Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
>
> Im pretty sure this should work (not able to test it myself just at
> the moment). If not, try:
>
> Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
>
> Sorry about not being sure ... I never have to write this stuff any
> more
as
> we have routines which do all this now.
>
> If neither of these work, please send us the exact error message.
>
> Good luck,
>
> David Barham
> Information Technology Consultant
> InformAtect Pty Ltd
> Brisbane, Australia
>
> -----Original Message-----
> From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> Sent: Thursday, 29 March 2001 22:17
> To: informix-datastage@oliver.com
> Subject: Re: char to date
>
> Thank you very much for your help guys.
>
> I managed to load the date field into my oracle target. Heres what I
did:
> - Used Iconv to convert the date field into DS internal format.
> - Changed the default datatype in DS as a result of importing oracle
> metadata from Timestamp to Date
>
> I did the following trial n error before I got it right:
> - Combined Oconv and Iconv in my expression. - still failed with the
> same error mesage
> - Removed Oconv in my expression. - still failed with the same error
message
> - Changed Timestamp data type to Date. - OK
>
> Now, oracle looks OK. But, when I view my data within Designer, that
> date field is displayed in DS internal date format. How can I change
> its
format
> to display the field in "Date" rather than DS date internal format?
>
> Please advise. Thanks.
>
> Rochelyn
>
> ----- Original Message -----
> From: "David Barham"
> To:
> Sent: Thursday, March 29, 2001 12:44 PM
> Subject: RE: char to date
>
>
> > The TimeStamp type in DataStage is basically a string in a very
> > specific format.
> >
> > To get a date out to Oracle you definitely need to use TimeStamp in
> > DataStage and date on the Oracle table definition. The metadata you
> > describe was generated correctly. Remember that Oracle dates hold
> > date
> and
> > time.
> >
> > TimeStamps need to have the date part in YYYY-MM-DD format, not the
> > YYYY-DD-MM format that you have.
> >
> > Use the Iconv and Oconv functions that Ray described to move the
> > date to
> the
> > timestamp column and that should solve your problem.
> >
> > Your expression where you move the date into the timestamp should be
> > something like (to slightly adjust Rays example to suit your
> > situation)
> >
> > Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
> >
> > I am not sure what standard functions there are to do this. Failing
> > anything else, I suggest that you create a transform for the above
> > expression.
> >
> > -----Original Message-----
> > From: rochelyn@summit.com.sg@netnet.com.sg
> > [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> > Sent: Thursday, 29 March 2001 14:23
> > To: informix-datastage@oliver.com
> > Subject: RE: char to date
> >
> > My source date field is in YYYY-DD-MM format. I want to load this
> > date field into my oracle table. I have an Oracle table to store
> > this info and the field
> > is defined as DATE data type. When I run my job, it gave the error
> message
> > as stated below.
> >
> > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START". Value treated as NULL
> > Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
> >
> >
> > When I checked my output table, the date column was null. I tried
> > to
> change
> > the data type in my Oracle table to VarChar, it went through. But,
> > this
> is
> > not what I want, I want my output field to be a DATE field as I have
> > originally defined in my table.
> >
> > What I mean by TIMESTAMP in my email below is, thats the TYPE that
> > was recognized by DataStage for my DATE (Orderdate) field when I
> > loaded the table definition
> > into Manager. I wasnt trying to create a timestamp. I hope I
understood
> > correctly
> > your point.
> >
> > Thanks.
> >
> > *******
> >
> > >When youre working with databases you (and therefore DataStage)
> > >must
> > follow
> >
> > >their rules. You need to provide a TIMESTAMP to Oracle, either by
> > >generating the timestamp in DataStage or by a date function in a
> > >user-defined Oracle INSERT statement.
> > >
> > >Look at the TIMESTAMP transform for one example of how you might
> accomplish
> >
> > >the former. There are other examples in the SDK, but these assume
> > >a different generic timestamp format.
> > >
> > >Be careful too that what youre getting from Progress may really be
> > >a
> date
> >
> > >(that is, a computer-format date rather than a human-readable date)
since
> > >its data type is DATE. You can use the DataStage debugger to see
> > >what youre actually getting. If its a computer-format date, you
> > >do not
need
> > the
> >
> > >Iconv() function.
> > >
> > >-----Original Message-----
> > >From: rochelyn@summit.com.sg@netnet.com.sg
> > >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> > >Sent: Thursday, 29 March 2001 10:45
> > >To: informix-datastage@oliver.com
> > >Subject: RE: char to date
> > >
> > >
> > >Thanks for your replies. I just want to make it clearer.
> > >
> > >My ODBC data source (Progress DB) is having a DATE field type. I
loaded
> > >this
> > >table definition into DataStage (Length = 10 Display = 10). My
> > >target
is
> > >Oracle8
> > >which already contains my target table. I defined the
> > >corresponding
> field
> >
> > >as
> > >DATE data type. I loaded this table definition into DataStage and
> > >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> > >
> > >When I run my job, I have this warning message:
> > >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START"
> >
> > >
> > >Value treated as NULL
> > >Attempt to convert String value "2099-22-09" to Timestamp type
> > unsuccessful.
> >
> > >
> > >
> > >Please advise. Thanks.
> > >
> > >Rochelyn
> > >
> > >*******
> > >
> > >>It really does depend on what your source is.
> > >>You say that it is char, so its not coming out of a database as a
Date
> > >>type.
> > >>
> > >>There are lots of Transforms in the SDK for manipulating dates.
> > >>Take
an
> > >>exploratory browse through these.
> > >>
> > >>The underlying function in pretty much all cases is Iconv() -
> > >>convert
on
> > >>input - or Oconv() - convert on output.
> > >>
> > >>So you convert your char string to a Date with Iconv(MyDate,
> > >>"DYMD"),
> > where
> >
> > >
> > >>the first "D" indicates that youre converting a date, and "YMD"
> indicates
> >
> > >
> > >>that it is in year-month-day order. This may be sufficient to
> > >>load
into
> a
> >
> > >
> > >>Date data type.
> > >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> > >>convert
it
> > >>into exactly the required format. Here the first "D" again
> > >>indicates
> that
> >
> > >
> > >>youre converting a date, the "-" is the delimiter character,
> > >>"YMD" indicates year-month-day order and [4,2,2] specifies four
> > >>digits for
the
> > >>year, two for the month and two for the day, so that month and day
will
> > get
> >
> > >
> > >>leading zeroes if necessary.
> > >>
> > >>The two can be combined. For example: Oconv(Iconv(MyDate,
> > >>"DYMD"), "D-YMD[4,2,2]")
> > >>
> > >>
> > >>-----Original Message-----
> > >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > >>Sent: Thursday, 29 March 2001 08:27
> > >>To: informix-datastage@oliver.com
> > >>Subject: char to date
> > >>
> > >>
> > >>I am a new user of this product, so I hope you dont mind me
> > >>asking
this
> > >>very simple question. I have a string field in my source but this
> > contains
> >
> > >
> > >>a date value, e.g. "1997-01-01", and I want to load this into my
target
> in
> >
> > >
> > >>date data type. What function / transform should I use?
> > >>
> > >>input as char: "1997-01-01"
> > >>output in target as date: 1997-01-01
> > >>
> > >>Thanks.
> > >>
> > >>
> > >
> > >
> >
>
Thanks for your reply.
I tried using: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]") without changing the data type to Date in DataStage but still failed. I also tried just: Iconv(input.MyDate, "DYDM"), still unsuccessful. Then, I decided to maintain this conversion and changed the datatype to Date instead of Timestamp, and I was able to load without errors into my target, but with the problem in "View Data" in DStage as mentioned.
Thank you for this new suggestion: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00". Ill try this out and will let you know.
Cheers!
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 11:52 PM
Subject: RE: char to date
> Rochelyn,
>
> The answer to your question about viewing the data in DataStage is to
change
> the column back to a timestamp.
>
> The DataStage timestamp is the "correct" way to handle Oracle dates.
>
> To refer back to your error:
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
> DataStage would fail to convert this string to timestamp because of
> the
day
> and month ordering.
>
> If your input column is input.MyDate, and it really is a string type
> with the value shown above, then the output field should be of type
> timestamp
as
> you described earlier and the expression should be
>
> Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
>
> Im pretty sure this should work (not able to test it myself just at
> the moment). If not, try:
>
> Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
>
> Sorry about not being sure ... I never have to write this stuff any
> more
as
> we have routines which do all this now.
>
> If neither of these work, please send us the exact error message.
>
> Good luck,
>
> David Barham
> Information Technology Consultant
> InformAtect Pty Ltd
> Brisbane, Australia
>
> -----Original Message-----
> From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> Sent: Thursday, 29 March 2001 22:17
> To: informix-datastage@oliver.com
> Subject: Re: char to date
>
> Thank you very much for your help guys.
>
> I managed to load the date field into my oracle target. Heres what I
did:
> - Used Iconv to convert the date field into DS internal format.
> - Changed the default datatype in DS as a result of importing oracle
> metadata from Timestamp to Date
>
> I did the following trial n error before I got it right:
> - Combined Oconv and Iconv in my expression. - still failed with the
> same error mesage
> - Removed Oconv in my expression. - still failed with the same error
message
> - Changed Timestamp data type to Date. - OK
>
> Now, oracle looks OK. But, when I view my data within Designer, that
> date field is displayed in DS internal date format. How can I change
> its
format
> to display the field in "Date" rather than DS date internal format?
>
> Please advise. Thanks.
>
> Rochelyn
>
> ----- Original Message -----
> From: "David Barham"
> To:
> Sent: Thursday, March 29, 2001 12:44 PM
> Subject: RE: char to date
>
>
> > The TimeStamp type in DataStage is basically a string in a very
> > specific format.
> >
> > To get a date out to Oracle you definitely need to use TimeStamp in
> > DataStage and date on the Oracle table definition. The metadata you
> > describe was generated correctly. Remember that Oracle dates hold
> > date
> and
> > time.
> >
> > TimeStamps need to have the date part in YYYY-MM-DD format, not the
> > YYYY-DD-MM format that you have.
> >
> > Use the Iconv and Oconv functions that Ray described to move the
> > date to
> the
> > timestamp column and that should solve your problem.
> >
> > Your expression where you move the date into the timestamp should be
> > something like (to slightly adjust Rays example to suit your
> > situation)
> >
> > Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
> >
> > I am not sure what standard functions there are to do this. Failing
> > anything else, I suggest that you create a transform for the above
> > expression.
> >
> > -----Original Message-----
> > From: rochelyn@summit.com.sg@netnet.com.sg
> > [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> > Sent: Thursday, 29 March 2001 14:23
> > To: informix-datastage@oliver.com
> > Subject: RE: char to date
> >
> > My source date field is in YYYY-DD-MM format. I want to load this
> > date field into my oracle table. I have an Oracle table to store
> > this info and the field
> > is defined as DATE data type. When I run my job, it gave the error
> message
> > as stated below.
> >
> > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START". Value treated as NULL
> > Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
> >
> >
> > When I checked my output table, the date column was null. I tried
> > to
> change
> > the data type in my Oracle table to VarChar, it went through. But,
> > this
> is
> > not what I want, I want my output field to be a DATE field as I have
> > originally defined in my table.
> >
> > What I mean by TIMESTAMP in my email below is, thats the TYPE that
> > was recognized by DataStage for my DATE (Orderdate) field when I
> > loaded the table definition
> > into Manager. I wasnt trying to create a timestamp. I hope I
understood
> > correctly
> > your point.
> >
> > Thanks.
> >
> > *******
> >
> > >When youre working with databases you (and therefore DataStage)
> > >must
> > follow
> >
> > >their rules. You need to provide a TIMESTAMP to Oracle, either by
> > >generating the timestamp in DataStage or by a date function in a
> > >user-defined Oracle INSERT statement.
> > >
> > >Look at the TIMESTAMP transform for one example of how you might
> accomplish
> >
> > >the former. There are other examples in the SDK, but these assume
> > >a different generic timestamp format.
> > >
> > >Be careful too that what youre getting from Progress may really be
> > >a
> date
> >
> > >(that is, a computer-format date rather than a human-readable date)
since
> > >its data type is DATE. You can use the DataStage debugger to see
> > >what youre actually getting. If its a computer-format date, you
> > >do not
need
> > the
> >
> > >Iconv() function.
> > >
> > >-----Original Message-----
> > >From: rochelyn@summit.com.sg@netnet.com.sg
> > >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> > >Sent: Thursday, 29 March 2001 10:45
> > >To: informix-datastage@oliver.com
> > >Subject: RE: char to date
> > >
> > >
> > >Thanks for your replies. I just want to make it clearer.
> > >
> > >My ODBC data source (Progress DB) is having a DATE field type. I
loaded
> > >this
> > >table definition into DataStage (Length = 10 Display = 10). My
> > >target
is
> > >Oracle8
> > >which already contains my target table. I defined the
> > >corresponding
> field
> >
> > >as
> > >DATE data type. I loaded this table definition into DataStage and
> > >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> > >
> > >When I run my job, I have this warning message:
> > >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START"
> >
> > >
> > >Value treated as NULL
> > >Attempt to convert String value "2099-22-09" to Timestamp type
> > unsuccessful.
> >
> > >
> > >
> > >Please advise. Thanks.
> > >
> > >Rochelyn
> > >
> > >*******
> > >
> > >>It really does depend on what your source is.
> > >>You say that it is char, so its not coming out of a database as a
Date
> > >>type.
> > >>
> > >>There are lots of Transforms in the SDK for manipulating dates.
> > >>Take
an
> > >>exploratory browse through these.
> > >>
> > >>The underlying function in pretty much all cases is Iconv() -
> > >>convert
on
> > >>input - or Oconv() - convert on output.
> > >>
> > >>So you convert your char string to a Date with Iconv(MyDate,
> > >>"DYMD"),
> > where
> >
> > >
> > >>the first "D" indicates that youre converting a date, and "YMD"
> indicates
> >
> > >
> > >>that it is in year-month-day order. This may be sufficient to
> > >>load
into
> a
> >
> > >
> > >>Date data type.
> > >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> > >>convert
it
> > >>into exactly the required format. Here the first "D" again
> > >>indicates
> that
> >
> > >
> > >>youre converting a date, the "-" is the delimiter character,
> > >>"YMD" indicates year-month-day order and [4,2,2] specifies four
> > >>digits for
the
> > >>year, two for the month and two for the day, so that month and day
will
> > get
> >
> > >
> > >>leading zeroes if necessary.
> > >>
> > >>The two can be combined. For example: Oconv(Iconv(MyDate,
> > >>"DYMD"), "D-YMD[4,2,2]")
> > >>
> > >>
> > >>-----Original Message-----
> > >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > >>Sent: Thursday, 29 March 2001 08:27
> > >>To: informix-datastage@oliver.com
> > >>Subject: char to date
> > >>
> > >>
> > >>I am a new user of this product, so I hope you dont mind me
> > >>asking
this
> > >>very simple question. I have a string field in my source but this
> > contains
> >
> > >
> > >>a date value, e.g. "1997-01-01", and I want to load this into my
target
> in
> >
> > >
> > >>date data type. What function / transform should I use?
> > >>
> > >>input as char: "1997-01-01"
> > >>output in target as date: 1997-01-01
> > >>
> > >>Thanks.
> > >>
> > >>
> > >
> > >
> >
>
Unless you know you must have a date in the source field, you should check for a null value and do something other than : " 00:00:00" with the result or thats all your target field will contain. This is not valid for Oracle.
Steve
----- Original Message -----
From: "Rochelyn Gamao"
To:
Sent: Thursday, March 29, 2001 11:09 AM
Subject: Re: char to date
> David,
>
> Thanks for your reply.
>
> I tried using: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
> without changing the data type to Date in DataStage but still failed.
> I also tried just: Iconv(input.MyDate, "DYDM"), still unsuccessful.
> Then,
I
> decided to maintain this conversion and changed the datatype to Date
instead
> of Timestamp, and I was able to load without errors into my target,
> but
with
> the problem in "View Data" in DStage as mentioned.
>
> Thank you for this new suggestion: Oconv(Iconv(input.MyDate, "DYDM"),
> "D-YMD[4,2,2]"):" 00:00:00". Ill try this out and will let you know.
>
> Cheers!
>
> Rochelyn
>
> ----- Original Message -----
> From: "David Barham"
> To:
> Sent: Thursday, March 29, 2001 11:52 PM
> Subject: RE: char to date
>
>
> > Rochelyn,
> >
> > The answer to your question about viewing the data in DataStage is
> > to
> change
> > the column back to a timestamp.
> >
> > The DataStage timestamp is the "correct" way to handle Oracle dates.
> >
> > To refer back to your error:
> >
> > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START". Value treated as NULL
> > Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
> >
> > DataStage would fail to convert this string to timestamp because of
> > the
> day
> > and month ordering.
> >
> > If your input column is input.MyDate, and it really is a string type
with
> > the value shown above, then the output field should be of type
> > timestamp
> as
> > you described earlier and the expression should be
> >
> > Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
> >
> > Im pretty sure this should work (not able to test it myself just at
> > the moment). If not, try:
> >
> > Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
> >
> > Sorry about not being sure ... I never have to write this stuff any
> > more
> as
> > we have routines which do all this now.
> >
> > If neither of these work, please send us the exact error message.
> >
> > Good luck,
> >
> > David Barham
> > Information Technology Consultant
> > InformAtect Pty Ltd
> > Brisbane, Australia
> >
> > -----Original Message-----
> > From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > Sent: Thursday, 29 March 2001 22:17
> > To: informix-datastage@oliver.com
> > Subject: Re: char to date
> >
> > Thank you very much for your help guys.
> >
> > I managed to load the date field into my oracle target. Heres what
> > I
> did:
> > - Used Iconv to convert the date field into DS internal format.
> > - Changed the default datatype in DS as a result of importing oracle
> > metadata from Timestamp to Date
> >
> > I did the following trial n error before I got it right:
> > - Combined Oconv and Iconv in my expression. - still failed with
> > the
same
> > error mesage
> > - Removed Oconv in my expression. - still failed with the same error
> message
> > - Changed Timestamp data type to Date. - OK
> >
> > Now, oracle looks OK. But, when I view my data within Designer,
> > that
date
> > field is displayed in DS internal date format. How can I change its
> format
> > to display the field in "Date" rather than DS date internal format?
> >
> > Please advise. Thanks.
> >
> > Rochelyn
> >
> > ----- Original Message -----
> > From: "David Barham"
> > To:
> > Sent: Thursday, March 29, 2001 12:44 PM
> > Subject: RE: char to date
> >
> >
> > > The TimeStamp type in DataStage is basically a string in a very
specific
> > > format.
> > >
> > > To get a date out to Oracle you definitely need to use TimeStamp
> > > in DataStage and date on the Oracle table definition. The
> > > metadata you describe was generated correctly. Remember that
> > > Oracle dates hold
date
> > and
> > > time.
> > >
> > > TimeStamps need to have the date part in YYYY-MM-DD format, not
> > > the YYYY-DD-MM format that you have.
> > >
> > > Use the Iconv and Oconv functions that Ray described to move the
> > > date
to
> > the
> > > timestamp column and that should solve your problem.
> > >
> > > Your expression where you move the date into the timestamp should
> > > be something like (to slightly adjust Rays example to suit your
situation)
> > >
> > > Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
> > >
> > > I am not sure what standard functions there are to do this.
> > > Failing anything else, I suggest that you create a transform for
> > > the above expression.
> > >
> > > -----Original Message-----
> > > From: rochelyn@summit.com.sg@netnet.com.sg
> > > [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of
> > > rochelyn
> > > Sent: Thursday, 29 March 2001 14:23
> > > To: informix-datastage@oliver.com
> > > Subject: RE: char to date
> > >
> > > My source date field is in YYYY-DD-MM format. I want to load this
date
> > > field
> > > into my oracle table. I have an Oracle table to store this info
> > > and
the
> > > field
> > > is defined as DATE data type. When I run my job, it gave the
> > > error
> > message
> > > as stated below.
> > >
> > > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > > "PC_START". Value treated as NULL
> > > Attempt to convert String value "2099-22-09" to Timestamp type
> > unsuccessful.
> > >
> > >
> > > When I checked my output table, the date column was null. I tried
> > > to
> > change
> > > the data type in my Oracle table to VarChar, it went through.
> > > But,
this
> > is
> > > not what I want, I want my output field to be a DATE field as I
> > > have originally defined in my table.
> > >
> > > What I mean by TIMESTAMP in my email below is, thats the TYPE
> > > that
was
> > > recognized
> > > by DataStage for my DATE (Orderdate) field when I loaded the table
> > > definition into Manager. I wasnt trying to create a timestamp.
> > > I hope I
> understood
> > > correctly
> > > your point.
> > >
> > > Thanks.
> > >
> > > *******
> > >
> > > >When youre working with databases you (and therefore DataStage)
> > > >must
> > > follow
> > >
> > > >their rules. You need to provide a TIMESTAMP to Oracle, either
> > > >by generating the timestamp in DataStage or by a date function in
> > > >a user-defined Oracle INSERT statement.
> > > >
> > > >Look at the TIMESTAMP transform for one example of how you might
> > accomplish
> > >
> > > >the former. There are other examples in the SDK, but these
> > > >assume a different generic timestamp format.
> > > >
> > > >Be careful too that what youre getting from Progress may really
> > > >be a
> > date
> > >
> > > >(that is, a computer-format date rather than a human-readable
> > > >date)
> since
> > > >its data type is DATE. You can use the DataStage debugger to see
what
> > > >youre actually getting. If its a computer-format date, you do
> > > >not
> need
> > > the
> > >
> > > >Iconv() function.
> > > >
> > > >-----Original Message-----
> > > >From: rochelyn@summit.com.sg@netnet.com.sg
> > > >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of
> > > >rochelyn
> > > >Sent: Thursday, 29 March 2001 10:45
> > > >To: informix-datastage@oliver.com
> > > >Subject: RE: char to date
> > > >
> > > >
> > > >Thanks for your replies. I just want to make it clearer.
> > > >
> > > >My ODBC data source (Progress DB) is having a DATE field type. I
> loaded
> > > >this
> > > >table definition into DataStage (Length = 10 Display = 10). My
target
> is
> > > >Oracle8
> > > >which already contains my target table. I defined the
> > > >corresponding
> > field
> > >
> > > >as
> > > >DATE data type. I loaded this table definition into DataStage
> > > >and
this
> > > >field
> > > >was recognized as TIMESTAMP (Length = 38 Display = 20).
> > > >
> > > >When I run my job, I have this warning message:
> > > >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > > "PC_START"
> > >
> > > >
> > > >Value treated as NULL
> > > >Attempt to convert String value "2099-22-09" to Timestamp type
> > > unsuccessful.
> > >
> > > >
> > > >
> > > >Please advise. Thanks.
> > > >
> > > >Rochelyn
> > > >
> > > >*******
> > > >
> > > >>It really does depend on what your source is.
> > > >>You say that it is char, so its not coming out of a database as
> > > >>a
> Date
> > > >>type.
> > > >>
> > > >>There are lots of Transforms in the SDK for manipulating dates.
Take
> an
> > > >>exploratory browse through these.
> > > >>
> > > >>The underlying function in pretty much all cases is Iconv() -
convert
> on
> > > >>input - or Oconv() - convert on output.
> > > >>
> > > >>So you convert your char string to a Date with Iconv(MyDate,
"DYMD"),
> > > where
> > >
> > > >
> > > >>the first "D" indicates that youre converting a date, and "YMD"
> > indicates
> > >
> > > >
> > > >>that it is in year-month-day order. This may be sufficient to
> > > >>load
> into
> > a
> > >
> > > >
> > > >>Date data type.
> > > >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
convert
> it
> > > >>into exactly the required format. Here the first "D" again
indicates
> > that
> > >
> > > >
> > > >>youre converting a date, the "-" is the delimiter character,
> > > >>"YMD" indicates year-month-day order and [4,2,2] specifies four
> > > >>digits for
> the
> > > >>year, two for the month and two for the day, so that month and
> > > >>day
> will
> > > get
> > >
> > > >
> > > >>leading zeroes if necessary.
> > > >>
> > > >>The two can be combined. For example: Oconv(Iconv(MyDate,
> > > >>"DYMD"), "D-YMD[4,2,2]")
> > > >>
> > > >>
> > > >>-----Original Message-----
> > > >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > > >>Sent: Thursday, 29 March 2001 08:27
> > > >>To: informix-datastage@oliver.com
> > > >>Subject: char to date
> > > >>
> > > >>
> > > >>I am a new user of this product, so I hope you dont mind me
> > > >>asking
> this
> > > >>very simple question. I have a string field in my source but
> > > >>this
> > > contains
> > >
> > > >
> > > >>a date value, e.g. "1997-01-01", and I want to load this into my
> target
> > in
> > >
> > > >
> > > >>date data type. What function / transform should I use?
> > > >>
> > > >>input as char: "1997-01-01"
> > > >>output in target as date: 1997-01-01
> > > >>
> > > >>Thanks.
> > > >>
> > > >>
> > > >
> > > >
> > >
> >
>
Steve
----- Original Message -----
From: "Rochelyn Gamao"
To:
Sent: Thursday, March 29, 2001 11:09 AM
Subject: Re: char to date
> David,
>
> Thanks for your reply.
>
> I tried using: Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
> without changing the data type to Date in DataStage but still failed.
> I also tried just: Iconv(input.MyDate, "DYDM"), still unsuccessful.
> Then,
I
> decided to maintain this conversion and changed the datatype to Date
instead
> of Timestamp, and I was able to load without errors into my target,
> but
with
> the problem in "View Data" in DStage as mentioned.
>
> Thank you for this new suggestion: Oconv(Iconv(input.MyDate, "DYDM"),
> "D-YMD[4,2,2]"):" 00:00:00". Ill try this out and will let you know.
>
> Cheers!
>
> Rochelyn
>
> ----- Original Message -----
> From: "David Barham"
> To:
> Sent: Thursday, March 29, 2001 11:52 PM
> Subject: RE: char to date
>
>
> > Rochelyn,
> >
> > The answer to your question about viewing the data in DataStage is
> > to
> change
> > the column back to a timestamp.
> >
> > The DataStage timestamp is the "correct" way to handle Oracle dates.
> >
> > To refer back to your error:
> >
> > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > "PC_START". Value treated as NULL
> > Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
> >
> > DataStage would fail to convert this string to timestamp because of
> > the
> day
> > and month ordering.
> >
> > If your input column is input.MyDate, and it really is a string type
with
> > the value shown above, then the output field should be of type
> > timestamp
> as
> > you described earlier and the expression should be
> >
> > Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]")
> >
> > Im pretty sure this should work (not able to test it myself just at
> > the moment). If not, try:
> >
> > Oconv(Iconv(input.MyDate, "DYDM"), "D-YMD[4,2,2]"):" 00:00:00"
> >
> > Sorry about not being sure ... I never have to write this stuff any
> > more
> as
> > we have routines which do all this now.
> >
> > If neither of these work, please send us the exact error message.
> >
> > Good luck,
> >
> > David Barham
> > Information Technology Consultant
> > InformAtect Pty Ltd
> > Brisbane, Australia
> >
> > -----Original Message-----
> > From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > Sent: Thursday, 29 March 2001 22:17
> > To: informix-datastage@oliver.com
> > Subject: Re: char to date
> >
> > Thank you very much for your help guys.
> >
> > I managed to load the date field into my oracle target. Heres what
> > I
> did:
> > - Used Iconv to convert the date field into DS internal format.
> > - Changed the default datatype in DS as a result of importing oracle
> > metadata from Timestamp to Date
> >
> > I did the following trial n error before I got it right:
> > - Combined Oconv and Iconv in my expression. - still failed with
> > the
same
> > error mesage
> > - Removed Oconv in my expression. - still failed with the same error
> message
> > - Changed Timestamp data type to Date. - OK
> >
> > Now, oracle looks OK. But, when I view my data within Designer,
> > that
date
> > field is displayed in DS internal date format. How can I change its
> format
> > to display the field in "Date" rather than DS date internal format?
> >
> > Please advise. Thanks.
> >
> > Rochelyn
> >
> > ----- Original Message -----
> > From: "David Barham"
> > To:
> > Sent: Thursday, March 29, 2001 12:44 PM
> > Subject: RE: char to date
> >
> >
> > > The TimeStamp type in DataStage is basically a string in a very
specific
> > > format.
> > >
> > > To get a date out to Oracle you definitely need to use TimeStamp
> > > in DataStage and date on the Oracle table definition. The
> > > metadata you describe was generated correctly. Remember that
> > > Oracle dates hold
date
> > and
> > > time.
> > >
> > > TimeStamps need to have the date part in YYYY-MM-DD format, not
> > > the YYYY-DD-MM format that you have.
> > >
> > > Use the Iconv and Oconv functions that Ray described to move the
> > > date
to
> > the
> > > timestamp column and that should solve your problem.
> > >
> > > Your expression where you move the date into the timestamp should
> > > be something like (to slightly adjust Rays example to suit your
situation)
> > >
> > > Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
> > >
> > > I am not sure what standard functions there are to do this.
> > > Failing anything else, I suggest that you create a transform for
> > > the above expression.
> > >
> > > -----Original Message-----
> > > From: rochelyn@summit.com.sg@netnet.com.sg
> > > [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of
> > > rochelyn
> > > Sent: Thursday, 29 March 2001 14:23
> > > To: informix-datastage@oliver.com
> > > Subject: RE: char to date
> > >
> > > My source date field is in YYYY-DD-MM format. I want to load this
date
> > > field
> > > into my oracle table. I have an Oracle table to store this info
> > > and
the
> > > field
> > > is defined as DATE data type. When I run my job, it gave the
> > > error
> > message
> > > as stated below.
> > >
> > > PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > > "PC_START". Value treated as NULL
> > > Attempt to convert String value "2099-22-09" to Timestamp type
> > unsuccessful.
> > >
> > >
> > > When I checked my output table, the date column was null. I tried
> > > to
> > change
> > > the data type in my Oracle table to VarChar, it went through.
> > > But,
this
> > is
> > > not what I want, I want my output field to be a DATE field as I
> > > have originally defined in my table.
> > >
> > > What I mean by TIMESTAMP in my email below is, thats the TYPE
> > > that
was
> > > recognized
> > > by DataStage for my DATE (Orderdate) field when I loaded the table
> > > definition into Manager. I wasnt trying to create a timestamp.
> > > I hope I
> understood
> > > correctly
> > > your point.
> > >
> > > Thanks.
> > >
> > > *******
> > >
> > > >When youre working with databases you (and therefore DataStage)
> > > >must
> > > follow
> > >
> > > >their rules. You need to provide a TIMESTAMP to Oracle, either
> > > >by generating the timestamp in DataStage or by a date function in
> > > >a user-defined Oracle INSERT statement.
> > > >
> > > >Look at the TIMESTAMP transform for one example of how you might
> > accomplish
> > >
> > > >the former. There are other examples in the SDK, but these
> > > >assume a different generic timestamp format.
> > > >
> > > >Be careful too that what youre getting from Progress may really
> > > >be a
> > date
> > >
> > > >(that is, a computer-format date rather than a human-readable
> > > >date)
> since
> > > >its data type is DATE. You can use the DataStage debugger to see
what
> > > >youre actually getting. If its a computer-format date, you do
> > > >not
> need
> > > the
> > >
> > > >Iconv() function.
> > > >
> > > >-----Original Message-----
> > > >From: rochelyn@summit.com.sg@netnet.com.sg
> > > >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of
> > > >rochelyn
> > > >Sent: Thursday, 29 March 2001 10:45
> > > >To: informix-datastage@oliver.com
> > > >Subject: RE: char to date
> > > >
> > > >
> > > >Thanks for your replies. I just want to make it clearer.
> > > >
> > > >My ODBC data source (Progress DB) is having a DATE field type. I
> loaded
> > > >this
> > > >table definition into DataStage (Length = 10 Display = 10). My
target
> is
> > > >Oracle8
> > > >which already contains my target table. I defined the
> > > >corresponding
> > field
> > >
> > > >as
> > > >DATE data type. I loaded this table definition into DataStage
> > > >and
this
> > > >field
> > > >was recognized as TIMESTAMP (Length = 38 Display = 20).
> > > >
> > > >When I run my job, I have this warning message:
> > > >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> > > "PC_START"
> > >
> > > >
> > > >Value treated as NULL
> > > >Attempt to convert String value "2099-22-09" to Timestamp type
> > > unsuccessful.
> > >
> > > >
> > > >
> > > >Please advise. Thanks.
> > > >
> > > >Rochelyn
> > > >
> > > >*******
> > > >
> > > >>It really does depend on what your source is.
> > > >>You say that it is char, so its not coming out of a database as
> > > >>a
> Date
> > > >>type.
> > > >>
> > > >>There are lots of Transforms in the SDK for manipulating dates.
Take
> an
> > > >>exploratory browse through these.
> > > >>
> > > >>The underlying function in pretty much all cases is Iconv() -
convert
> on
> > > >>input - or Oconv() - convert on output.
> > > >>
> > > >>So you convert your char string to a Date with Iconv(MyDate,
"DYMD"),
> > > where
> > >
> > > >
> > > >>the first "D" indicates that youre converting a date, and "YMD"
> > indicates
> > >
> > > >
> > > >>that it is in year-month-day order. This may be sufficient to
> > > >>load
> into
> > a
> > >
> > > >
> > > >>Date data type.
> > > >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
convert
> it
> > > >>into exactly the required format. Here the first "D" again
indicates
> > that
> > >
> > > >
> > > >>youre converting a date, the "-" is the delimiter character,
> > > >>"YMD" indicates year-month-day order and [4,2,2] specifies four
> > > >>digits for
> the
> > > >>year, two for the month and two for the day, so that month and
> > > >>day
> will
> > > get
> > >
> > > >
> > > >>leading zeroes if necessary.
> > > >>
> > > >>The two can be combined. For example: Oconv(Iconv(MyDate,
> > > >>"DYMD"), "D-YMD[4,2,2]")
> > > >>
> > > >>
> > > >>-----Original Message-----
> > > >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> > > >>Sent: Thursday, 29 March 2001 08:27
> > > >>To: informix-datastage@oliver.com
> > > >>Subject: char to date
> > > >>
> > > >>
> > > >>I am a new user of this product, so I hope you dont mind me
> > > >>asking
> this
> > > >>very simple question. I have a string field in my source but
> > > >>this
> > > contains
> > >
> > > >
> > > >>a date value, e.g. "1997-01-01", and I want to load this into my
> target
> > in
> > >
> > > >
> > > >>date data type. What function / transform should I use?
> > > >>
> > > >>input as char: "1997-01-01"
> > > >>output in target as date: 1997-01-01
> > > >>
> > > >>Thanks.
> > > >>
> > > >>
> > > >
> > > >
> > >
> >
>
Glad youve got it working! The Data Browser is a WYSIWYG - what you see is what youve got. Thats the whole point. If you want to see external (human-readable) dates, you use the Oconv() function. Then you can see dates as you expect, but not as Oracle expects!
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 20:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 20:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
Since I tried using Oconv with my expression but failed, does it mean I cant view my date field in "human-readable" format within DStage if my output is Oracle?
----- Original Message -----
From: Ray Wurlod
To:
Sent: Friday, 30 March 2001 8:35
Subject: RE: char to date
Glad youve got it working! The Data Browser is a WYSIWYG - what you see is what youve got. Thats the whole point. If you want to see external (human-readable) dates, you use the Oconv() function. Then you can see dates as you expect, but not as Oracle expects!
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 20:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>
----- Original Message -----
From: Ray Wurlod
To:
Sent: Friday, 30 March 2001 8:35
Subject: RE: char to date
Glad youve got it working! The Data Browser is a WYSIWYG - what you see is what youve got. Thats the whole point. If you want to see external (human-readable) dates, you use the Oconv() function. Then you can see dates as you expect, but not as Oracle expects!
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, 29 March 2001 20:17
To: informix-datastage@oliver.com
Subject: Re: char to date
Thank you very much for your help guys.
I managed to load the date field into my oracle target. Heres what I did:
- Used Iconv to convert the date field into DS internal format.
- Changed the default datatype in DS as a result of importing oracle metadata from Timestamp to Date
I did the following trial n error before I got it right:
- Combined Oconv and Iconv in my expression. - still failed with the same error mesage
- Removed Oconv in my expression. - still failed with the same error message
- Changed Timestamp data type to Date. - OK
Now, oracle looks OK. But, when I view my data within Designer, that date field is displayed in DS internal date format. How can I change its format to display the field in "Date" rather than DS date internal format?
Please advise. Thanks.
Rochelyn
----- Original Message -----
From: "David Barham"
To:
Sent: Thursday, March 29, 2001 12:44 PM
Subject: RE: char to date
> The TimeStamp type in DataStage is basically a string in a very
> specific format.
>
> To get a date out to Oracle you definitely need to use TimeStamp in
> DataStage and date on the Oracle table definition. The metadata you
> describe was generated correctly. Remember that Oracle dates hold
> date
and
> time.
>
> TimeStamps need to have the date part in YYYY-MM-DD format, not the
> YYYY-DD-MM format that you have.
>
> Use the Iconv and Oconv functions that Ray described to move the date
> to
the
> timestamp column and that should solve your problem.
>
> Your expression where you move the date into the timestamp should be
> something like (to slightly adjust Rays example to suit your
> situation)
>
> Oconv(Iconv(InputDate, "DYDM"), "D-YMD[4,2,2]")
>
> I am not sure what standard functions there are to do this. Failing
> anything else, I suggest that you create a transform for the above
> expression.
>
> -----Original Message-----
> From: rochelyn@summit.com.sg@netnet.com.sg
> [mailto:rochelyn@summit.com.sg@netnet.com.sg] On Behalf Of rochelyn
> Sent: Thursday, 29 March 2001 14:23
> To: informix-datastage@oliver.com
> Subject: RE: char to date
>
> My source date field is in YYYY-DD-MM format. I want to load this
> date field into my oracle table. I have an Oracle table to store this
> info and the field
> is defined as DATE data type. When I run my job, it gave the error
message
> as stated below.
>
> PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START". Value treated as NULL
> Attempt to convert String value "2099-22-09" to Timestamp type
unsuccessful.
>
>
> When I checked my output table, the date column was null. I tried to
change
> the data type in my Oracle table to VarChar, it went through. But,
> this
is
> not what I want, I want my output field to be a DATE field as I have
> originally defined in my table.
>
> What I mean by TIMESTAMP in my email below is, thats the TYPE that
> was recognized by DataStage for my DATE (Orderdate) field when I
> loaded the table definition
> into Manager. I wasnt trying to create a timestamp. I hope I understood
> correctly
> your point.
>
> Thanks.
>
> *******
>
> >When youre working with databases you (and therefore DataStage) must
> follow
>
> >their rules. You need to provide a TIMESTAMP to Oracle, either by
> >generating the timestamp in DataStage or by a date function in a
> >user-defined Oracle INSERT statement.
> >
> >Look at the TIMESTAMP transform for one example of how you might
accomplish
>
> >the former. There are other examples in the SDK, but these assume a
> >different generic timestamp format.
> >
> >Be careful too that what youre getting from Progress may really be a
date
>
> >(that is, a computer-format date rather than a human-readable date)
> >since its data type is DATE. You can use the DataStage debugger to
> >see what youre actually getting. If its a computer-format date, you
> >do not need
> the
>
> >Iconv() function.
> >
> >-----Original Message-----
> >From: rochelyn@summit.com.sg@netnet.com.sg
> >[mailto:rochelyn@summit.com.sg@netnet.com.sg]On Behalf Of rochelyn
> >Sent: Thursday, 29 March 2001 10:45
> >To: informix-datastage@oliver.com
> >Subject: RE: char to date
> >
> >
> >Thanks for your replies. I just want to make it clearer.
> >
> >My ODBC data source (Progress DB) is having a DATE field type. I
> >loaded this table definition into DataStage (Length = 10 Display =
> >10). My target is Oracle8
> >which already contains my target table. I defined the corresponding
field
>
> >as
> >DATE data type. I loaded this table definition into DataStage and
> >this field was recognized as TIMESTAMP (Length = 38 Display = 20).
> >
> >When I run my job, I have this warning message:
> >PC2.pc_mstr_EFS: At row 1, link "output", while processing column
> "PC_START"
>
> >
> >Value treated as NULL
> >Attempt to convert String value "2099-22-09" to Timestamp type
> unsuccessful.
>
> >
> >
> >Please advise. Thanks.
> >
> >Rochelyn
> >
> >*******
> >
> >>It really does depend on what your source is.
> >>You say that it is char, so its not coming out of a database as a
> >>Date type.
> >>
> >>There are lots of Transforms in the SDK for manipulating dates.
> >>Take an exploratory browse through these.
> >>
> >>The underlying function in pretty much all cases is Iconv() -
> >>convert on input - or Oconv() - convert on output.
> >>
> >>So you convert your char string to a Date with Iconv(MyDate,
> >>"DYMD"),
> where
>
> >
> >>the first "D" indicates that youre converting a date, and "YMD"
indicates
>
> >
> >>that it is in year-month-day order. This may be sufficient to load
> >>into
a
>
> >
> >>Date data type.
> >>Or you may need to use Oconv(InternalDate, "D-YMD[4,2,2]") to
> >>convert it into exactly the required format. Here the first "D"
> >>again indicates
that
>
> >
> >>youre converting a date, the "-" is the delimiter character, "YMD"
> >>indicates year-month-day order and [4,2,2] specifies four digits for
> >>the year, two for the month and two for the day, so that month and
> >>day will
> get
>
> >
> >>leading zeroes if necessary.
> >>
> >>The two can be combined. For example:
> >>Oconv(Iconv(MyDate, "DYMD"), "D-YMD[4,2,2]")
> >>
> >>
> >>-----Original Message-----
> >>From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> >>Sent: Thursday, 29 March 2001 08:27
> >>To: informix-datastage@oliver.com
> >>Subject: char to date
> >>
> >>
> >>I am a new user of this product, so I hope you dont mind me asking
> >>this very simple question. I have a string field in my source but
> >>this
> contains
>
> >
> >>a date value, e.g. "1997-01-01", and I want to load this into my
> >>target
in
>
> >
> >>date data type. What function / transform should I use?
> >>
> >>input as char: "1997-01-01"
> >>output in target as date: 1997-01-01
> >>
> >>Thanks.
> >>
> >>
> >
> >
>