phillip.small wrote:
The input data type is a character and I am converting it to a date column in this format (YYYYMMDD). However, the OCONV function still put the dashes in the output. I do not want the dashes in the table. That is what I am trying to do. Also the DIGITS function output looks like this: 9811-06-11) something like that. They are screwing up the years. What to do?
Just move the value. And set target field to be Date if you wish.
There is no conversion I think. It is all ASCII.
phillip.small wrote:
The input data type is a character and I am converting it to a date column in this format (YYYYMMDD). However, the OCONV function still put the dashes in the output. I do not want the dashes in the table. That is what I am trying to do. Also the DIGITS function output looks like this: 9811-06-11) something like that. They are screwing up the years. What to do?
Just move the value. And set target field to be Date if you wish.
There is no conversion I think. It is all ASCII.
After doing the straight value, the output came in this format: 7385-10-08
If you have a string of text that looks anything like a date, use ICONV to turn it into an integer date value that DS understands: ICONV(yourdate, "D").
Now, to format that internal integer value into a formatted date, use OCONV: OCONV(yourinternaldate, "D-YMD")
So, in your case, your value already looks like a date. Remember, DS Server treats all fields like STRINGS.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
First - the solution you asked for is: "DYMD[4'',2'',2]"
[Two single quotes next to each other, following the 4 and the first 2] This says you want nothing as the delimiter. As others have said, I think you are wanting OCONV, not ICONV.
Second - please tell us more of what you are doing. What is the source, what database are you going to? Much of this thread could have been avoided (and you would have had an answer sooner) if the details were included.
ds_developer wrote:First - the solution you asked for is: "DYMD[4'',2'',2]"
[Two single quotes next to each other, following the 4 and the first 2] This says you want nothing as the delimiter. As others have said, I think you are wanting OCONV, not ICONV.
Second - please tell us more of what you are doing. What is the source, what database are you going to? Much of this thread could have been avoided (and you would have had an answer sooner) if the details were included.
Hope this helps,
John
I will tell again. The source field is a character coming from an Oracle table. It has the date looking format (20041001) and the target database is DB2 and has a Date data type. The derivation I just ran has given me the correct data but the dashes are still there.
Iconv(DSLink9.DA_EFFDT1,"DYMD[4'',2'',2]")
What I am trying to do is get rid of the dashes and if need be convert it and ICONV seems to be the one that returns the correct data. Maybe I might need to use OCONV inside of ICONV?
Your target is DB2, that little nugget of information makes all the difference in the world.
The DB2 plugin has this pesky quirk, unlike ALL OTHER STAGES, that likes the date in the INTERNAL format. Search this forum, we've covered this lots of times.
Either set your metadata on the DATE column to CHAR, or, slap an ICONV on your date column to internalize it for the plugin. Or, switch to ODBC and use that instead.
The DB2 plugin reads and writes dates in the INTERNAL form, not the forum human beings typically read. This is a constant irritant for all users of the DB2 plugin.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
MCN will never put dashes in its output. It only extracts the numbers which from 0 to 9. The reason why you are getting the dashes is your target data element is defined as date data type. Change the data type to Character and run the job it should work. If you have your target as date date type then whatever your input may be will be converted to its date value so make sure you choose the right data type. Don't use Integer since the leading 0 will be truncated.
The source field is a character coming from an Oracle table. It has the date looking format (20041001) and the target database is DB2 and has a Date data type.
The most efficient way to get that date into internal format is to rely on the innate cleverness of the Iconv function. All you need to specify is the year, month, day order.
DataStage can figure out quite happily for itself whether there are delimiters.
And that's all you need for the DB2 plug-in stage which, as Kenneth mentioned, expects the DataStage internal format.
The internal format of 2004-10-01 is 13424. That is, the result of calling Iconv("2004-10-01", "DYMD") or Iconv("20041001", "DYMD") or Iconv("01102004", "DDMY") or Iconv("10012004", "DMDY"), etc., is 13424.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
