Varchar to Date format

[pxraja]

If you wish. It's a viable alternative. ...

If I am altering the target table and changing the data type from date to varchar means,

the conversion is working fine Its code is

Oconv(Iconv(Inlink.Field1,'D-YMD[4,2,2]'),'D/DMY[2,2,4]')

but with the date data type its not populating any records though its running without any warnings.

I can say that Oracle table is not accepting the conversion, Is there any alternatives to achieve the conversion with DATE data type?

Any suggestions are welcome

Thanks in Advance

[chulett]

Suggest you actually answer the question I posted.

[pxraja]

The other important question is - is the stage generating sql for you or are you using custom sql? Generated sql will use a TO_DATE() and expect your data in YYYY-MM-DD HH24:MI:SS format, which ...

Hi Craig,

I am using custom sql, and I am not using any TO_DATE() Function. Have I answered your question? or missing anything?

Any suggestions?

Thanks in advance

[pxraja]

Can any one clarify that the functions like Iconv,Oconv,TIMESTAMP.TO.DATE will work for Varchar datatype in oracle 10g

or

it will also work for DATE datatype also in Oracle10g

Thanks in advance

[ray.wurlod]

Once the data get into DataStage server job there are no data types. Therefore any function can be applied to any "data type".

Of course you have to present data in the correct format to any target that requires a specific format. In that case of Oracle that format is typically specified things like the date picture (the second argument of a TO_DATE() or TO_CHAR() function).

[chulett]

I am using custom sql, and I am not using any TO_DATE() Function.

I would have bet the house that was the answer.

Learn how Oracle DATE fields work, in particular in conjunction with the TO_CHAR() function when selecting one and TO_DATE() when loading one. Talk to your DBA on the subject.

You need to know what format your date is ending up with and use the appropriate (matching) TO_DATE function mask so Oracle can parse it properly. For example, '2008-09-22' would need to loaded using:

Code: Select all

TO_DATE(YourField, 'YYYY-MM-DD')

There are also a plethora of conversations to be found here on the subject, including in-depth discussions on why I always use a Timestamp datatype in my jobs for Oracle DATE fields and specifically control the time portion, even the zero times.

[tennetiharika]

If you are handling this in a view then use(to_date(to_char())).
If it is coming in ur file and u have to take it from file in Oracle stage the ODBC itself will convert it to char first (while selecting) and while insrting it will conevrt into date and insert.But u only have to c that u use ORACLE8i ODBC(new version)

[ray.wurlod]

Please write in English.

[chulett]

Exactly. Eight posts so far and all a mess. This is not a social networking site nor your dang cell phone, it is a professional site where professionals support each other... well, professionally. Do you write your emails at work as poorly? Your documentation?

[pxraja]

Thanks for your support,

I had a conversation with DBA, the Oracle Default Date format is MM/DD/YYYY and when using TO_DATE() in custom sql the data is populated into target table with data type DATE.

Now I had created another job for populating into target table with DATE datatype.

first job

ODBC----->TRF--------->ODBC (data type is Varchar)

second job

ODBC(custom sql using TO_DATE())------->TRF--------->ODBC(DATE datatype)

Can anyone suggest me how to change 20080922 format as 22/09/2008
in single job

Thanks in Advance

[ray.wurlod]

Yes, but don't.

If you're using ODBC you must follow the ODBC rules (YYYY-MM-DD for dates), not the Oracle rules (whatever those have been set to).

[pxraja]

Yes, but don't.

If you're using ODBC you must follow the ODBC rules (YYYY-MM-DD for dates), not the Oracle rules (whatever those have been set to). ...

Hi Ray,

what I Understood from your response is I can develop a single job but it is not advisable, Is that right?

anyhow can you make it clear how can I do it in single job? any clues?

Thanks in advance

[ray.wurlod]

Which particular "it"? There have been so many in this thread! Take a step back, and write out a specification in English (with no mention of any ETL tool) what it is you want to accomplish. And let's begin again from there.

Remember that there are NO DATA TYPES in server jobs.

[chulett]

I had a conversation with DBA, the Oracle Default Date format is MM/DD/YYYY and when using TO_DATE() in custom sql the data is populated into target table with data type DATE.

Never worry about what the 'default date' (NLS_DATE) format is. When you use TO_DATE() all that matters is that your mask/picture matches the data. It will always load fine regardless of the 'default' format, which is what makes it a Best Practice.

So make the date look however you like and match the TO_DATE() mask to it. And this will work for OCI and ODBC but to avoid the issue Ray notes declare the field as a Varchar rather than a Date.

[pxraja]

Which particular "it"? There have been so many in this thread! Take a step back, and write out a specification in English (with no mention of any ETL tool) what it is you want to accomplish. And le ...

that is, changing the date format from 20080922 to 22/09/2008. How to obtain this conversion with a single job instead of two jobs what I had created?

any suggestions are welcome

Thanks in advace