Attempt to convert String value "2005-12-14 " to D

Shadab_Farooque · Post by **Shadab_Farooque** » Mon Jul 16, 2007 6:17 am

Hi,

I am using a routine for Date field.
However, I am getting the error as "Attempt to convert String value "2005-12-14 " to Date type unsuccessful"
The input date is in format "20070716" (in YYYYMMDD) Format.
I have to load this in Oracle table.The column in Oracle Table is "Date".

Please advise

chulett · Post by **chulett** » Mon Jul 16, 2007 6:23 am

What does your target SQL look like? There should be a TO_DATE() function there with a matching mask.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 16, 2007 6:47 pm

You assertion is incorrect: the incoming date is NOT in YYYYMMDD format. The error message informs you that it is in YYYY-MM-DD format. Be more general with your specification to the Iconv() function - just specify "DYMD", which will handle both formats.

chulett · Post by **chulett** » Mon Jul 16, 2007 9:03 pm

Sounds to me like the 'input' is indeed YYYYMMDD and the 'routine' (whatever it is) is successfully converting it to YYYY-MM-DD. Oracle then rejects it as there is no TO_DATE() conversion specified and it is not in the default NLS_DATE format for the target database. Or the generated mask doesn't match what's being supplied, but that would generally throw a different error.

Rather than always trying to target whatever the default date format is for any given database, best to assume nothing and use a generated TO_DATE() function with the proper mask so the default date format doesn't matter. IMHO.

Shadab_Farooque · Post by **Shadab_Farooque** » Mon Jul 16, 2007 10:31 pm

Hi Chelett,

You are right,the input date is in YYYYMMDD formay and the routine is successfully converting it to MM-DD-YYYY.I also wrote the routine to convert the input date to DD-MON-YY format,but still encountering the above error.
Can you tell me wheteher TO_DATE() conversion has to be specified.
The generated sql query look likes

UPDATE table1 Country_Code=:2,Country_Name=:3,Description=:4,Start_Date=TO_DATE(:5, 'DD-MON-YY'),Warehouse_Update_Date=TO_DATE(:6, 'DD-MON-YY') WHERE W_Key=:1;
INSERT INTO MWV_BUCKET_DIM (W_Key,Country_Code,country_Name,Description,Start_Date,Warehouse_Update_Date) VALUES (:1,:2,:3,:4,TO_DATE(:5, 'DD-MON-YY'),TO_DATE(:6, 'DD-MON-YY')).

Please advise where elso TO_DATE() has to be used in order to get rid of the error.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 16, 2007 10:52 pm

String value "2005-12-14", complained of in the error message, does NOT look like MM-DD-YYYY format to me.

chulett · Post by **chulett** » Mon Jul 16, 2007 11:03 pm

Nor does it look like DD-MON-YY which is what your TO_DATE (which you obviously already have) is expecting. Your routine will need to produce a date in *that* format for it to work with that generated sql. Doing that properly can't give you "the above" error again, if you think it is - post your latest error message.