Hi,
Could anybody suggest me some solution for the following requirement?
1. How do I get the current system date in TimeStamp datatype using BASIC functions?
2. How can I perform a subtraction of n number of days from a date of TimeStamp datatype? The result should also be a TimeStamp.
I have tried with TimeDate() function which is returning Date data type which when I have passed to oracle table gave a runtime error.
Thanks in advance,
Srinivas.
Handling the Date datatype
Moderators: chulett, rschirm, roy
-
dlvsrinivas
- Participant
- Posts: 18
- Joined: Mon Jul 07, 2003 12:56 am
- Location: India
Srinivas
There are 2 ways to get system date, @DATE and DATE(). The first is date that you connected to the DataStage engine. The second calls the OS and gets the current date. The corresponding time functions are @TIME and TIME(). You format these using oconv() and iconv(). They can format a date or time any way you want it. I always try to keep timestamps as varchars in the metadata. It is easier to deal with. There are several routines in the SDK to format timestamps. There is source code that you can go look at to see how they do it. A couple of the routines are specific for Oracle. To subtract two timestamps and return a timestamp should be written as a routine. Basically you need to convert to internal versions of the date part and the time part and subtract the two parts and then put them back together. If you need examples then search through this forum. There are lots of examples.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
There are 2 ways to get system date, @DATE and DATE(). The first is date that you connected to the DataStage engine. The second calls the OS and gets the current date. The corresponding time functions are @TIME and TIME(). You format these using oconv() and iconv(). They can format a date or time any way you want it. I always try to keep timestamps as varchars in the metadata. It is easier to deal with. There are several routines in the SDK to format timestamps. There is source code that you can go look at to see how they do it. A couple of the routines are specific for Oracle. To subtract two timestamps and return a timestamp should be written as a routine. Basically you need to convert to internal versions of the date part and the time part and subtract the two parts and then put them back together. If you need examples then search through this forum. There are lots of examples.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I was facing the same questions and found that DataStage doesn't have a datatype that contains both the date and time portion of a timestamp. They must be handled separately. The internal representation of a date is an integer as the number of days since '1967-12-31', while a time is the number of seconds since midnight. To get the current point in time in a timestamp format (I hope we agree that this is "YYYY-MM-DD hh:mm:ss") you can use: oconv(Date(), "D-YMD[4,2,2]"):" ":oconv(Time(), "MTS"), while TimeDate() would return the same information as "hh:mm:ss DD MM YYYY". Direct calculation isn't possible on both of them, with the first format you can at least compare 2 values ('>', '
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is a perfect case where a Transform is ideal. I call this Transform NowAsTimeStamp.
It has a single input argument, which it ignores. It generates a TimeStamp. Its derivation expression is:
Oconv(Date(),"D-YMD[4,2,2]"):" ":Oconv(Time(),"MTS:")
Any place I need it, I can re-use this component.
If you need it not to change during execution of the job, substitute @DATE for Date() and @TIME for Time(), as Kim suggests.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
It has a single input argument, which it ignores. It generates a TimeStamp. Its derivation expression is:
Oconv(Date(),"D-YMD[4,2,2]"):" ":Oconv(Time(),"MTS:")
Any place I need it, I can re-use this component.
If you need it not to change during execution of the job, substitute @DATE for Date() and @TIME for Time(), as Kim suggests.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518