DSXchange

Hi,

I try to put current date to Oracle. The Oracle type on the target table is Date. If I use the function Date(), then it is a 13310. But if I use the function Oconv, then it becomes blank on the target table.

I do I handle this.

Post your Oconv logic. I'd also suggest defining it as a Timestamp in the OCI stage and consistently handling them that way.

If your oracle target is OCI, the date needs to be in it's native format which in the generated SQL code will use a to_date conversion function.

The Oracle stage adds to_Date(columnname, 'YYYY-MM-DD') to your column in the auto-generated SQL. If you're using auto-generated SQL, then make sure your date value looks like YYYY-MM-DD. Using DATE() returns the internal date format, so use something like OCONV(@DATE, 'D-YMD[4,2,2]'). If you're using custom SQL, then you must insure that your date value gets the appropriate to_date logic to cast the DS supplied value into the form the database accepts.

Hi Ken,

I tried your way, it doesn't work either.
Here is the error message:

Attempt to convert String value "2004-06-09" to Date type unsuccessful

kcbland wrote:The Oracle stage adds to_Date(columnname, 'YYYY-MM-DD') to your column in the auto-generated SQL. If you're using auto-generated SQL, then make sure your date value looks like YYYY-MM-DD. Using DATE() returns the internal date format, so use something like OCONV(@DATE, 'D-YMD[4,2,2]'). If you're using custom SQL, then you must insure that your date value gets the appropriate to_date logic to cast the DS supplied value into the form the database accepts.

Hi,
Try this
OCONV(@DATE, "D-YMD[4,2,2]")

1. @DATE system variable - The internal date when the program started
It return integer value.

2. When this is passed to OCONV it returns the date as 2004-06-09

Ketfos

acool wrote:Hi Ken,

I tried your way, it doesn't work either.
Here is the error message:

Attempt to convert String value "2004-06-09" to Date type unsuccessful

kcbland wrote:The Oracle stage adds to_Date(columnname, 'YYYY-MM-DD') to your column in the auto-generated SQL. If you're using auto-generated SQL, then make sure your date value looks like YYYY-MM-DD. Using DATE() returns the internal date format, so use something like OCONV(@DATE, 'D-YMD[4,2,2]'). If you're using custom SQL, then you must insure that your date value gets the appropriate to_date logic to cast the DS supplied value into the form the database accepts.

Well, are you using Auto-gen'd SQL or custom? If you're using custom, did you remember to put the to_date logic on the date column?

Hi

Please try StringToTimestamp(date) or else use Timedate system function.

Please use TimeDate( ) function...

Hi,
AFAIK, when using the date data type in native stages the transformation from internal DS date repersentation should be transparent.
so if you have a number (DS internal date) it should work automatically.
I had this working once when moving dates from Informix to SQL Server,
I used the date data type when exctracting the date from IFX, with no transformation what so ever, and simply inserted to SQL Server with date data type (using OLE DB I think).
(I guess I need to check it on ORACEL )

IHTH,

I use custom SQL, with to_date.

kcbland wrote:

acool wrote:Hi Ken,

I tried your way, it doesn't work either.
Here is the error message:

Attempt to convert String value "2004-06-09" to Date type unsuccessful

kcbland wrote:The Oracle stage adds to_Date(columnname, 'YYYY-MM-DD') to your column in the auto-generated SQL. If you're using auto-generated SQL, then make sure your date value looks like YYYY-MM-DD. Using DATE() returns the internal date format, so use something like OCONV(@DATE, 'D-YMD[4,2,2]'). If you're using custom SQL, then you must insure that your date value gets the appropriate to_date logic to cast the DS supplied value into the form the database accepts.

Well, are you using Auto-gen'd SQL or custom? If you're using custom, did you remember to put the to_date logic on the date column?