How to use Session Date in Transformer Stage

[Sreenivas]

Hi,

I am trying to use DSJobStartDate to get the Current Date into Transformer Stage and to pass the same to Database. The system is throwing the following warning. Please suggest how to use the System Date not Datetime.

K_FDM84_88_JOBCODE_TBL..k_exp_JOBCODE_TBL: At row 1, link "k_exp_JOBCODE_TBL", while processing column "LAST_UPDATE_DATE"
Value treated as NULL
Attempt to convert String value "2005-12-19" to Date type unsuccessful

Thanks,
Sreenivas

[ppalka]

In job properties select Parameters tab and then click Add environment variable and then New. In a name put 'date' and in prompt '$ENV'. And now you can use that variable in transformer. It will return system date. In default value put also '$ENV' and when you run the job you should put value '$ENV' for that variable.

[chulett]

You are getting the system date successfully, as is obvious from your error message. What you're not doing is handling it correctly.

Need more information about what you are doing to provide specific help. What target database, target field, stage used, target datatype, etc. You'll more than likely need to do more than simply dump the macro into the field's derivation, depending on your answers to the above questions.

[Sreenivas]

You are getting the system date successfully, as is obvious from your error message. What you're not doing is handling it correctly.

Need more information about what you are doing to provide specific help. What target database, target field, stage used, target datatype, etc. You'll more than likely need to do more than simply dump the macro into the field's derivation, depending on your answers to the above questions.

Thanks for the reply,
I tried the following options
using DSJobStartDate Macro, Oconv(Date(),"D/MDY[2,2,4]"). The Target Database id DB2 and the datatype is Date with 10 as field length. I am also using the same Datetype and field length in my stage and this is Nullable. It is saying "Attempt to convert String value "12/19/2005" to Date type unsuccessful".

I will appreciate your response.

Thanks,
Sreenivas

[chulett]

Search the forum. From what I recall, the DB2 stage seems to want dates in internal format rather than the traditional external format you are using.

[Titto]

You are getting the system date successfully, as is obvious from your error message. What you're not doing is handling it correctly.

Need more information about what you are doing to provide specific help. What target database, target field, stage used, target datatype, etc. You'll more than likely need to do more than simply dump the macro into the field's derivation, depending on your answers to the above questions.

Thanks for the reply,
I tried the following options
using DSJobStartDate Macro, Oconv(Date(),"D/MDY[2,2,4]"). The Target Database id DB2 and the datatype is Date with 10 as field length. I am also using the same Datetype and field length in my stage and this is Nullable. It is saying "Attempt to convert String value "12/19/2005" to Date type unsuccessful".

I will appreciate your response.

Thanks,
Sreenivas

Hi Sreenivas,

Hope this will work for you

Code: Select all

Iconv("27/05/1997", "D/E")

will convert your sting data to internal format and insert that data to your target DB2 table.