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.
![Confused :?](./images/smilies/icon_confused.gif)
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:
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:
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.
![Idea :idea:](./images/smilies/icon_idea.gif)
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.