Page 2 of 3
Posted: Tue Sep 23, 2008 6:26 am
by pxraja
ray.wurlod wrote: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
Posted: Tue Sep 23, 2008 7:34 am
by chulett
Suggest you actually answer the question I posted.
Posted: Tue Sep 23, 2008 11:30 pm
by pxraja
chulett wrote: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
Posted: Wed Sep 24, 2008 4:58 am
by 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
Posted: Wed Sep 24, 2008 5:06 am
by 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).
Posted: Wed Sep 24, 2008 7:27 am
by chulett
pxraja wrote: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:
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.
Posted: Thu Sep 25, 2008 5:04 am
by 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)
Posted: Thu Sep 25, 2008 5:13 am
by ray.wurlod
Please write in English.
Posted: Thu Sep 25, 2008 6:40 am
by 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?
Posted: Fri Sep 26, 2008 1:03 am
by 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
Posted: Fri Sep 26, 2008 1:07 am
by 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).
Posted: Fri Sep 26, 2008 1:36 am
by pxraja
ray.wurlod wrote: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
Posted: Fri Sep 26, 2008 2:20 am
by 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.
Posted: Fri Sep 26, 2008 7:31 am
by chulett
pxraja wrote: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.
Posted: Sat Sep 27, 2008 2:31 am
by pxraja
ray.wurlod wrote: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