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