Inserting sysdate into Timestamp field

nelc · Post by **nelc** » Mon Apr 28, 2003 1:50 am

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!

luca · Post by **luca** » Mon Apr 28, 2003 2:16 am

Oconv(@DATE,"D-MDY[4,2,2]")":" ":Oconv(@TIME,"MTS")
for getting the system date/time when the job started (constant for one run).
Date() Time() function to get the date/time at the moment of the call of the function (changing for the same execution).

luca · Post by **luca** » Mon Apr 28, 2003 2:18 am

One " overly:
Oconv(@DATE,"D-MDY[4,2,2]"):" ":Oconv(@TIME,"MTS")
will work.

chulett · Post by **chulett** » Mon Apr 28, 2003 7:04 am

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? [:)]

mhester · Post by **mhester** » Mon Apr 28, 2003 12:47 pm

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

chulett · Post by **chulett** » Mon Apr 28, 2003 12:59 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Apr 28, 2003 4:59 pm

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

nelc · Post by **nelc** » Wed Apr 30, 2003 3:12 am

Hi all!

Thank you so much for the help!

[:)]