Thanks for our reply David. I wish datastage handled
dates as easily as Oracle does. Requirements on date
arithmetic is uniform, regardless of source/target
database characteristics. The date & time conversions
(via Oconv & Inconv) are okay but handling midnights
and leap years are another cause of concern.. I was
just hoping that there would be a function in DS !
coming to max ... my understanding was that max
doesnt do comparisions across columns. Please correct
me if I am wrong.
Thanks
B
> Oracle and UniVerse (DataStage) handle dates
> differently.
>
> Oracle has a DATE datatype which holds date and
> time. This is held
> internally as the number of days (or fractions there
> of) since some time in
> ancient history. The fractional component
> represents the part of a day, for
> example 0.5 represents 12 midday.
>
> Universe holds date and time separately. The
> experts can correct me if Im
> wrong here but my understanding is that Universe
> does not really have date
> or time types as such. Various routines are
> supplied which assume dates and
> times are held as follows:
>
> Dates are held as an integer and are the number of
> days since some day in
> recent history (I forget which). Note that
> negatives are allowed so that
> dates prior to this reference date are handled.
>
> Time is held as a separate value and represents the
> number of seconds since
> midnight.
>
>
> Now to DataStage. DataStage, in order to interact
> with various external
> databases, recognises additional datatypes. Of
> particular interest to you
> is the TimeStamp which DataStage uses to handle the
> Oracle (et al) DATE
> type. However, TimeStamp is basically just a string
> which holds a date/time
> in the format that Oracle would describe as
> YYYY-MM-DD HH24:MI:SS.
>
> IF your date/time is in the standard TimeStamp
> format, then obviously,
> relative comparisons and functions like MAX are
> going to work.
>
> To do date and time arithmetic, you are going to
> have to convert the
> TimeStamp back to date and time values (refer to the documentation on
> Iconv and Oconv) before you can do this. We wrote a
> routine which adds/subtracts
> a number of seconds to a TimeStamp. Fairly simple
> to write and a good
> exercise for you to get your head around date time
> handling.
>
> Have fun.
>
> -----Original Message-----
> From: Bibhu C [mailto:
bibhuds@yahoo.com]
> Sent: Wednesday, 17 October 2001 8:15 AM
> To:
datastage-users@oliver.com
> Subject: questions on date arithmetic
>
> I have a situation where my target dates is derived
> from two source dates.
> Here is the logic.
>
> 1. Target_DT1 = max(Source_DT1, Source_DT2)
>
> ("max" should work similar to the "greatest"
> function
> in Oracle)
>
> 2. Target_DT2 = Target_DT1 - 1 second
>
> e.g. If my source row is
>
> Source_DT1 Source_DT2
> 10-10-2001 10:20:36 10-15-2001 12:23:16
>
> then my target record should look like
>
> Target_DT1 Target_DT2
> 10-15-2001 12:23:16 10-15-2001 12:23:15
>
> I am unable to do both the max and the date
> arithmetic
> in Datastage. Can someone show me light ?
>
> thanks
> Bibhu
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com