date conversion

[ravitell1]

Hello All

I am reading a field name JOINING_DT from DB2 as
"12167 (2001-04-23)"
JOINING_DT datatype is Date.
I need to convert to the above format to MMDDYYYY.
When i tried to use the field and extract the date part it is giving me NULL.

Thanks in advance

[ray.wurlod]

What syntax are you using for the Field() function? Is what is being retrieved 18 characters long? Then you could extract the YMD format date using Field() functions.

Code: Select all

Field(Field(InLink.TheString, "(", 2, 1), ")", 1, 1)

Store this in a stage variable. Apply an Iconv() function to this to covert to internal format, then an Oconv() function to convert to the desired string format you specified.

Code: Select all

Oconv(Iconv(svTheDate), "DYMD"), "DMDY" : @VM : "MCN")

[ravitell1]

when i try to use this Field(SRC_JOINING_DT,' ',2) it is giving me NULL.
It is not returning any value..

[kumar_s]

Check if there is a space in your input string.
Hope know that you are trying to get the second part of your input string based on the delimiter as space.
Try the getting one number. Try using the double qoute.

Field(SRC_JOINING_DT," " ,2,1)

[ray.wurlod]

Maybe the character is not a space character, but "white space" (a tab, perhaps). Did you try what I suggested, or was I just wasting my time?

[arunkumarmm]

when i try to use this Field(SRC_JOINING_DT,' ',2) it is giving me NULL.
It is not returning any value..

Or you can try this

Code: Select all

Field(Field(SRC_JOINING_DT,')',1),'(',2)

[Mike]

The DB2 API stage in server jobs communicates with DB2 using DataStage internal dates for date data types.

Is "12167 (2001-04-23)" what you are seeing when you do a view data?

That is exactly what I would expect view data to show me for a DB2 date column defined as a date in DataStage. It is just how view data represents the internal date and has nothing to do with how it is stored internally.

12167 is the internal date value (an integer), and (2001-04-23) is an external representation of 12167 to help us humans understand the value.

You should be able to just do a simple OConv on the internal date value to format any external representation that you like.

Mike

[ravitell1]

Oconv worked.. Thanks bro.