Newbie here. Trying to set sysdate value in the one of the columns in transformer stage for loading into Oracle9i DB.
The destination column is in Timestamp type. Which function/system variable should I use? I have tried the following: @DATE, Date(), TimeDate(). All of which returns a string literal format does not match error.
Any help is greatly appreciated. Thanks in advance!
Inserting sysdate into Timestamp field
Moderators: chulett, rschirm, roy
What luca is pointing out is that you seldom get the information you need directly in the format that you need it. Once you've identified where you are going to get it from (in this case the DATA() & TIME()functions) and what it looks like, you can take advantage of DataStage's very powerful transform capabilities to get it into the format that you need. Oconv & Iconv are two examples.
Another approach is to use SYSDATE in the OCI stage directly, much as you would do on your own. You would need to use User-defined Sql to accomplish that.
-craig
Hey luca, you know you can edit your own posts - right? [:)]
Another approach is to use SYSDATE in the OCI stage directly, much as you would do on your own. You would need to use User-defined Sql to accomplish that.
-craig
Hey luca, you know you can edit your own posts - right? [:)]
nelc,
Another way to transform this type of date/time would be to use the supplied routine -
DateTimeStampToOraOCIWithTime
This is part of the sdk and you're keeping things standard from the standpoint that if someone were to look at your job they would instantly know what kind of date/time you were converting to. Remember that there is an sdk with the toolset and it can be very useful.
Michael
Another way to transform this type of date/time would be to use the supplied routine -
DateTimeStampToOraOCIWithTime
This is part of the sdk and you're keeping things standard from the standpoint that if someone were to look at your job they would instantly know what kind of date/time you were converting to. Remember that there is an sdk with the toolset and it can be very useful.
Michael
Yes, the sdk contains some very useful routines, especially as a source of inspiration for your own. [:D]
Keep in mind that the 'DateTimeStamp' routines all expect the date in 'SDK' format, which is documented in each of the routines. It's also interesting to note that the DateCurrentGMTTime routine says that it returns an SDK formatted timestamp, but the format it uses is *different* than all of the DateTimeStamp routines are expecting. The 'DateGenericToTimeStamp' seems to do it properly. FYI.
-craig
Keep in mind that the 'DateTimeStamp' routines all expect the date in 'SDK' format, which is documented in each of the routines. It's also interesting to note that the DateCurrentGMTTime routine says that it returns an SDK formatted timestamp, but the format it uses is *different* than all of the DateTimeStamp routines are expecting. The 'DateGenericToTimeStamp' seems to do it properly. FYI.
-craig
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Another possibility is to use user-defined SQL, in which case you can use SYSDATE and all your other favourite constructs directly, with parameter markers to provide the information from DataStage. For example (assuming column C2 is a DATE type):
INSERT INTO table(C1,C2,C3) VALUES (:1, SYSDATE, :2);
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
INSERT INTO table(C1,C2,C3) VALUES (:1, SYSDATE, :2);
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518