date is an integer
Moderators: chulett, rschirm, roy
date is an integer
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.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
doesn't work
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
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.
Re: doesn't work
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?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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Re: doesn't work
I use custom SQL, with to_date.
kcbland wrote: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?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.