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
questions on date arithmetic
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
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
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
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