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
date conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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
Field(Field(InLink.TheString, "(", 2, 1), ")", 1, 1)
Code: Select all
Oconv(Iconv(svTheDate), "DYMD"), "DMDY" : @VM : "MCN")
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.
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.
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)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
Or you can try thisravitell1 wrote:when i try to use this Field(SRC_JOINING_DT,' ',2) it is giving me NULL.
It is not returning any value..
Code: Select all
Field(Field(SRC_JOINING_DT,')',1),'(',2)
Arun
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
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