converting timestamp to date format

[pxraja]

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

[pxraja]

converting to get the quarter field

[ray.wurlod]

Code: Select all

Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

Code: Select all

QUARTER.TAG(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"))

[pxraja]

Code: Select all

Oconv(Iconv(DSLink2.Field1,"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

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

[chulett]

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.

[ray.wurlod]

Code: Select all

Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

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]

[pxraja]

Code: Select all

Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter t ...

Hi all,

Thanks for your ideas and suggestions, the above quote has resolved the problem