DSXchange

Posted: **Fri Aug 20, 2004 12:45 pm**

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.

Posted: **Fri Aug 20, 2004 1:05 pm**

leomauer wrote:
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

Posted: **Fri Aug 20, 2004 1:13 pm**

phillip.small wrote: After doing the straight value, the output came in this format: 7385-10-08

Do not code any conversion in derivation, just bring the value over to new field unchanged.[/img]

Posted: **Fri Aug 20, 2004 1:19 pm**

leomauer wrote:
phillip.small wrote: After doing the straight value, the output came in this format: 7385-10-08
Do not code any conversion in derivation, just bring the value over to new field unchanged.[/img]

That is what I did. DATE1 --> DATE1

I brought the value over with no derivations at all. It is because the char to date data type, I believe.

Posted: **Fri Aug 20, 2004 1:33 pm**

phillip.small wrote:
leomauer wrote:
phillip.small wrote: After doing the straight value, the output came in this format: 7385-10-08
Do not code any conversion in derivation, just bring the value over to new field unchanged.[/img]
That is what I did. DATE1 --> DATE1

I brought the value over with no derivations at all. It is because the char to date data type, I believe.

It works for me. I just tried to do anything to corrupt the value and I couldn't. Sorry.

Posted: **Fri Aug 20, 2004 1:41 pm**

This is irritating.

IF YOU ALREADY HAVE A DATE, YOU DO NOT USE ICONV.

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.

Posted: **Fri Aug 20, 2004 1:45 pm**

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

Posted: **Fri Aug 20, 2004 2:00 pm**

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?

Posted: **Fri Aug 20, 2004 2:21 pm**

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.

Posted: **Fri Aug 20, 2004 4:02 pm**

Hi Phillips,
Just use the Erepalce function as I mentioned to the output you get.
It will remove the "-" character from your data.

Also DIGITS Transform is availabe in 5.0

Ketfos

Posted: **Sat Aug 21, 2004 3:34 am**

Use an extra Ereplace().

Ogmios

Posted: **Sat Aug 21, 2004 8:43 pm**

Hi

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.

Thanks
Siva

Posted: **Mon Aug 23, 2004 1:24 am**

Use
Trim(Oconv(Iconv(InputDate,"D-YMD[4,2,2]"),"DYMD[4,2,2]")," ","A")

This will give you the desired output. "InputDate" is the date in
YYYY-MM-DD Format.

Posted: **Mon Aug 23, 2004 1:37 am**

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.

Code: Select all

Iconv(TheDate, "DYMD")

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.

DSXchange

Date Conversion