char to date: obvious but useful display options
char to date: obvious but useful display options
This is a topic for an orphaned message.
If its solely for debugging/design purposes, I realize these are the obvious suggestions and mean having another expression, but you could:
1) Add a transform column in the same "to oracle" link, with the "visible" date format you want, and then use the debugger interactively when/if you want to see it from the designer. This is a run time solution for "row by
row" viewing, but could prove very useful. The debugger doesnt get used
often enough, but can be very useful in selective situations.
2) The viewer is part of a passive (target)stage type, so send a nicely formatted column (or a subset of the rows ...use a constraint and limit the output link to rows of @INROWNUM < "n") to a sequential stage. All you need is a filename, no need to consider other naming issues and locations -- let DS put it where it wants to, especially if you are exclusively using it for limited viewing in the designer, after a job completes. Theres some overhead there, so Im not sure Id want to leave this laying around in production jobs with enormous volumes, but it will help with the development effort.
Ernie
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, March 29, 2001 9:19 PM
To: informix-datastage@oliver.com
Subject: Re: char to date
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.
> >>
> >>
> >
> >
>
1) Add a transform column in the same "to oracle" link, with the "visible" date format you want, and then use the debugger interactively when/if you want to see it from the designer. This is a run time solution for "row by
row" viewing, but could prove very useful. The debugger doesnt get used
often enough, but can be very useful in selective situations.
2) The viewer is part of a passive (target)stage type, so send a nicely formatted column (or a subset of the rows ...use a constraint and limit the output link to rows of @INROWNUM < "n") to a sequential stage. All you need is a filename, no need to consider other naming issues and locations -- let DS put it where it wants to, especially if you are exclusively using it for limited viewing in the designer, after a job completes. Theres some overhead there, so Im not sure Id want to leave this laying around in production jobs with enormous volumes, but it will help with the development effort.
Ernie
-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Thursday, March 29, 2001 9:19 PM
To: informix-datastage@oliver.com
Subject: Re: char to date
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.
> >>
> >>
> >
> >
>