I want to load system date

[ranga1970]

Hi,
My database oracle 9 and A filed is defined as of TIMESTAMP(6),
while loading a record, I want to load system date in to this filed, could some one help on this, I tried @date, Date(), but it dint help,

with out this filed data is being loaded in to table perfectly, now I want to load system date here can some one help?

thanks
ranga

[chulett]

How did you try to use them? They can be made to work just fine as long as they are formatted properly. A couple of questions for you:

1) Out of curiousity, how are you defining the TIMESTAMP field in the job? As far as I know, the OCI stage doesn't really support these new datatypes.

2) Do you want the exact same 'system date' on each record in the load, so they can be tracked together, or do you want the actual system time of insert on each record?

For #1, you'd need to use one of the conv brothers (Oconv or IConv) on the function of choice to get the output into the correct format. If you were using a traditional DATE field with a Timestamp datatype in the job, you'd need to get it into YYYY-MM-DD HH24:MI:SS format, for example. Not sure what the TIMESTAMPs expect.

The @DATE System Variable returns the date the 'job' started in internal format. The Date() function returns the current date in internal format. The TimeDate() function returns the system time and date in external format. All of this is in your online help. You would then need to manipulate that to get the result into the appropriate format.

For #2 you could either use a static value or capture it once in the 'Initial Value' of a Stage Variable and then assign that to each record if you wanted each record to have the exact same timestamp. Alternately, make the call to get the system time in the derivation of the field so that it happens on every record processed.

Or switch to custom SQL and do not mention the TIMESTAMP field in the output stage. Let the stage handle all of the other fields and then add in the TIMESTAMP field yourself. In the 'VALUES' clause, add a call to SYSDATE. That should work fine as long as you are going for the 'every record gets a unique timestamp' approach. You may need to take this approach if you find the OCI stages are not properly handling the new TIMESTAMP types.

[ranga1970]

I would like to load just date, time is not required, it is going to insert date,
now what your suggestion for this call @date in stage variable and then call the stagevariable while loadign every record, thats what I did and it could not be loaded
my question is now do I need to use Oconv and I conv functions for this? if yes could some explainf,
First aasign to stage variable @date and now how do I use Iconv and Oconv, or it is enough if se one of that?
I just need to iload insert date, for that record, no time, please suggest best possible approach

thanks
ranga

[vigneshra]

Hi Ranga,

Why cannot you put CurrentDate() in the derivation part of the field. What is the need for you to go for stage variables and @date function. I don't know because I used to work with DB2 database and I don't know about Oracle OCI stage. I just wanted to make sure that you know about the CurrentDate() function. If it is helpful to you just let us know.

[Sainath.Srinivasan]

If you want to load without the time, use the OConv to convert into YYYY-MM-DD and concatenate the time as '00:00:00'.

[chulett]

I'm not familiar with any 'CurrentDate' function but there are several things like that you could use. Ones like DateCurrentDateTime or simply DATE.TAG with @DATE if you don't want the time.

That's the confusing part for me. I'm surprised you've got one of the new TIMESTAMP fields and all you want in it is a date. Especially a TIMESTAMP(6) which means you want the fractional seconds (which is the whole point behind using one of these) down to six points of precision. However, moving forward on just a date...

Iconv and Oconv are needed for all kinds of conversions, dates included. It would greatly help to become proficient with them. Check out the included Date Transforms that manipulate them into various formats to get a feel for how they work.

For example, the DATE.TAG transform in the Built-In category. Double-click on it in the Manager and then check the Long Description to see what it expects and what it does with it. You'll find it simply does an 'Oconv' to get an internal date into YYYY-MM-DD format. So, you could use that or do the Oconv yourself.

I was suggesting that you do it in the Initial Value field of a Stage Variable so it is only called once rather than once for every row. So the derivation could be:

Code: Select all

DATE.TAG(@DATE)

And you'll get the current date back out in 'YYYY-MM-DD' format. You could also use Oconv directly to accomplish the same thing:

Code: Select all

Oconv(@DATE,"D-YMD[4,2,2")

These two are equivalent.

HOWEVER, this only solves part of the problem. As I said before, I do not believe that the OCI stages natively support these new datatypes - if someone knows differently, please chime in. As far as I know, you need to use a TO_TIMESTAMP function in much the same manner that the stage will use TO_DATE for a DATE field, but the stage won't do automagically that for you. This means a fall-back to custom sql.

As noted by our terse friend, you'll need to append a 'zero time' if you only want the date portion, so this changes our derivation to something like this:

Code: Select all

DATE.TAG(@DATE) : " 00:00:00.000000"

You could then use the TO_TIMESTAMP function with the appropriate format mask in the target stage to load this string into your target field. I'll leave that part up to you (and perhaps your DBA) to put together.

Or skip the whole darn stage-variable-formatting thing and let Oracle do the work for you. I'm sure custom sql in the target stage where you don't mention the TIMESTAMP field in the job and simply fill it with SYSDATE during the insert would work just fine as well.

[ranga1970]

Code: Select all

INSERT INTO ODSOWNER.MEMBER_SSN_CONVERSION_TBL (MEMBER_ID,MEMBER_ID_SUFFIX,OLD_MEMBER_ID,OLD_MEMBER_ID_SUFFIX,MEMBER_INSERT_DATE) VALUES (:1,:2,:3,:4,TO_TIMESTAMP(Sysdate, 'YYYY-MM-DD HH24:MI:SS'))

does this work? any other changes required?

[chulett]

It may, give it a shot and let us know.

Make sure you only have the first 4 fields in the Column tab of the OCI stage. Also, try it first with just SYSDATE in the fifth 'value' position, I'm not positive you need to actually use the function in that case. If it doesn't work like that, try it again with the TO_TIMESTAMP.

Also, you should TRUNC the sysdate so you don't get the time portion.