Oracle Varchar to Oracle Date

dak · Post by **dak** » Tue Mar 15, 2005 10:41 am

Attempting to convert an Oracle 8i VARCHAR(255) column i.e(01/01/2001) to an Oracle 8i DATE column using a Transformation Stage. I get an error for invalid Date/Time Format.

I checked the generated SQL in the Insert stage and it shows the following (TO_DATE(:15, 'YYYY-MM-DD HH24:MI:SS')) whether I am using the Generic Date to ORA or ORA with Time transformation.

I checked other posts and tried the suggestions to fix other individuals problems such as play with the metadata and just check and be sure the generated sql looks correct. So I can the the SQL to look like the following (TO_DATE(:15, 'DD-MON-YY)), but than I get an OCI Date conversion failed.

I am just looking for any other suggestions.

Thanks in advance.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Mar 15, 2005 10:45 am

I assume you are saying that you have selected 'User defined SQL' and made change to TO_DATE format.

Is that error from DataStage? If yes, then make the metadata as varchar(255) than date.

dak · Post by **dak** » Tue Mar 15, 2005 10:53 am

I am not using 'User defined SQL' this is all DS generated SQL.

Yes it is a DS error. I have tried input metadata to transformation stage as VARCHAR(255) and the output metadata to Oracle 8i as DATE data elment = 'date'. Generated SQL looks like (TO_DATE(:15, 'DD-MON-YY)) and I get an DS error called OCI Date conversin failed.

Should I not be using a deviration in the transformation stage?

dak · Post by **dak** » Tue Mar 15, 2005 10:56 am

without the deriviration in the transformation stage I get the following DS error:

Value treated as NULL
Attempt to convert String value "06/14/1955" to Date type unsuccessful

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Mar 15, 2005 10:57 am

Leave the target format to be YYYY-MM-DD HH24:MI:SS.

In the tranformer, include

OConv(IConv(YourVarcharDateColumn, 'D/DMY[2.2,4]'), 'D-YMD[4,2,2]') : ' 00:00:00'

dak · Post by **dak** » Tue Mar 15, 2005 12:10 pm

Thanks Sainath. Now I understand.

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 4:22 am

Sainath.Srinivasan wrote:Leave the target format to be YYYY-MM-DD HH24:MI:SS.

In the tranformer, include

OConv(IConv(YourVarcharDateColumn, 'D/DMY[2.2,4]'), 'D-YMD[4,2,2]') : ' 00:00:00'

Even I had the same problem and when I tried your logic it is giving me this error
RNK_SEQOCI_V3_cus..Transformer_29: ORA-01843: not a valid month. The reason is the date field is populated as 00:00:00.
Any thought on this?

ray.wurlod · Post by **ray.wurlod** » Fri Oct 14, 2005 4:46 am

In the USA, use MDY rather than DMY in the expression.

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 4:48 am

ray.wurlod wrote:In the USA, use MDY rather than DMY in the expression.

Ray,
I used the user defined sql and the only to get away with this error message is that. So I copied the generated sql and wherever there is a TO_DATE, I checked according to what I want and it worked.

Thanks

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 5:34 am

dprasanth wrote:
ray.wurlod wrote:In the USA, use MDY rather than DMY in the expression.
Ray,
I used the user defined sql and the only to get away with this error message is that. So I copied the generated sql and wherever there is a TO_DATE, I checked according to what I want and it worked.
Thanks

One more small doubt, can I put a decode statement in the user defined sql. The reason why i am asking u this is.. I have go the few date fields which have NULL(nulls are hardcoded as NULL), so I am trying to put the following decode statement in the Insert which I am doing.

DECODE(:42,'NULL',null,TO_DATE(:42,'DD-MM-YYYY')). But I am getting the following error message
RNK_SEQOCI_V3_cus..Transformer_29: ORA-01401: inserted value too large for column

Thanks in Advance.

kcbland · Post by **kcbland** » Fri Oct 14, 2005 6:50 am

Dates are REALLY easy, you don't have to do most of the manipulations discussed on this thread.

1. Put all dates into ISO format YYYY-MM-DD during transformation. If you have a NULL value, THAT'S OKAY. However, whenever you manipulate your date into ISO, make sure you do it correctly. The time portion is NOT needed for DATE, just Timestamp, so adding '00:00:00' is unnecessary. When you do add it, make sure you have a date value otherwise don't append.
2. Make sure your data type for your date fields show as DATE. ORAOCI stages automatically put TO_DATE() onto the SQL for DATE datatype columns.
3. Use generated SQL in the OCI stages.

That's it. No need to use user-defined SQL. No torturing columns with NVL and TO_CHAR, etc.

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 6:55 am

kcbland wrote:Dates are REALLY easy, you don't have to do most of the manipulations discussed on this thread.

1. Put all dates into ISO format YYYY-MM-DD during transformation. If you have a NULL value, THAT'S OKAY. However, whenever you manipulate your date into ISO, make sure you do it correctly. The time portion is NOT needed for DATE, just Timestamp, so adding '00:00:00' is unnecessary. When you do add it, make sure you have a date value otherwise don't append.
2. Make sure your data type for your date fields show as DATE. ORAOCI stages automatically put TO_DATE() onto the SQL for DATE datatype columns.
3. Use generated SQL in the OCI stages.

That's it. No need to use user-defined SQL. No torturing columns with NVL and TO_CHAR, etc.

When you say ISO format , do you mean to change the NLS?If that is the case I am using ISO8859-1 as my project default.

kcbland · Post by **kcbland** » Fri Oct 14, 2005 7:10 am

You're not the original poster, you've hijacked that persons thread. So, to speak to their original post:

I checked the generated SQL in the Insert stage and it shows the following (TO_DATE(:15, 'YYYY-MM-DD HH24:MI:SS')) whether I am using the Generic Date to ORA or ORA with Time transformation

Generated SQL puts the TO_DATE on all columns with DATE data type. All you have to do is make sure your dates match the date token. In this posters case, it's 'YYYY-MM-DD HH24:MI:SS', which I referred to as the ISO date. In non-NLS environments, this is always the case. So, just make sure your dates are YYYY-MM-DD in structure and everything works without any extra effort.

I believe your problems came from using the suggested code by the poster to do OCONV(ICONV... logic with a concatentation of '00:00:00', which did not take into consideration a blank or null date. You're better off using one of the SDK date Functions/Transforms instead of that logic.

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 7:12 am

Sorry I am new to the forum , that is the reason why i havent quoted properly. I wil try the way u have suggested

ray.wurlod · Post by **ray.wurlod** » Fri Oct 14, 2005 8:19 am

"ISO format" for dates means ISO 8601, which is an international standard for how to format dates, timestamps, times. The standard date format is YYYY-MM-DD.

ISO8859 (various parts) is a standard relating to encoding of characters, and is irrelevant on this thread. NLS character maps don't come into it at all. However, if you have NLS enabled, check that your locale TIME category has been chosen appropriately.

When I referred earlier to "MDY" instead of "DMY", I meant in the Oconv() and Iconv() functions, not in SQL.