date is an integer

[acool]

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.

[chulett]

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

[1stpoint]

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.

[kcbland]

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.

[acool]

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

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.

[ketfos]

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

[kcbland]

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

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?

[mandyli]

Hi

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

[afssekar]

Please use TimeDate( ) function...

[roy]

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,

[acool]

I use custom SQL, with to_date.

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

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?