Hi all,
I am trying to convert the get the quarter for Outlink.field1 column from the Inlink.field1 having timestamp in the format YDM and my code for the conversion is as follows
Oconv(Iconv(TIMESTAMP.TO.DATE( DSLink2.field1),'YDM[4,2,2]'),'YMD[4,2,2]')
Job runs successfully throwing warnings but not writing any records in the Outlink.field1
SQLSTATE=23000, DBMS.CODE=1400
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("RELSTAGING"."STG_CDA_PRTL_PRD_DUMMY"."CURR_CYCLE_DATE")
scd1..STG_CDA_PRTL_PRD_DRC_AGN_OVACR_Trf (Timestamp) transform error: Invalid timestamp input '2008-02-12 00:00:00.000'
Your suggestions are welcomed to retrieve this
thanks in advance
converting timestamp to date format
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")
Code: Select all
QUARTER.TAG(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"))
Last edited by ray.wurlod on Thu Aug 14, 2008 3:42 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod wrote:Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):Code: Select all
Oconv(Iconv(DSLink2.Field1,"DYMD"),"DQ")
Code: Select all
QUARTER.TAG(Iconv(DSLink2.Field1, ...[/quote] Thanks ray As I am coding as quoted like, Oconv(Iconv(DSLink2.,"DYMD"),"DQ") job runs successfully without warnings (since i alter the target oracle table modifying NOTNULL to allow NULL) but the field1 is NULL.. actual datatype of Inlink.Field1 is Timestamp(23,3) so the data is '2008-02-12 00:00:00.000' and the Outlink.Field1 is Timestamp(3,3) Whether it could be the problem of getting null values ? I could not get in to the problem identification. any suggestions are welcomed Thanks in advance
As Ray notes, substring the date out then convert. IMHO, neither field should be declared a Timestamp in your job as neither fits that definition, use Varchar for the source and decimal/integer/numeric for the target.
Last edited by chulett on Thu Aug 14, 2008 3:47 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
pxraja wrote:ray.wurlod wrote:Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):Code: Select all
Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")
Code: Select all
QUARTER.TAG(Iconv(Field(DSLink2.Field1," ",1,1), ...[/quote] Thanks ray As I am coding as quoted like, Oconv(Iconv(DSLink2.,"DYMD"),"DQ") job runs successfully without warnings (since i alter the target oracle table modifying NOTNULL to allow NULL) but the field1 is NULL.. actual datatype of Inlink.Field1 is Timestamp(23,3) so the data is '2008-02-12 00:00:00.000' and the Outlink.Field1 is Timestamp(3,3) Whether it could be the problem of getting null values ? I could not get in to the problem identification. any suggestions are welcomed Thanks in advance[/quote]
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi all,ray.wurlod wrote:pxraja wrote:ray.wurlod wrote:Or, if you want an ISO 8601 "quarter t ...Code: Select all
Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")
Thanks for your ideas and suggestions, the above quote has resolved the problem