Date Conversion

[phillip.small]

Iconv(LinkName.DateColumnName, "DYMD[4,2,2]")

The following code example returns the date in this format: 2003-10-01

How do I modify this to return it in this format: 20031001?

[chulett]

You could use:

Code: Select all

DIGITS(Iconv(LinkName.DateColumnName, "DYMD[4,2,2]"))

I believe it always wants to add a delimiter to the date. This uses a Transform to strip out all non-numeric characters.

[phillip.small]

You could use:

Code: Select all

DIGITS(Iconv(LinkName.DateColumnName, "DYMD[4,2,2]"))

I believe it always wants to add a delimiter to the date. This uses a Transform to strip out all non-numeric characters.

The dashes are still there. How do I get rid of the dashes? thank for your help!!!

[kcbland]

OCONV formats dates from the internal integer value, not ICONV which tries to recognize dates and turn them into the internal integer value.

[ketfos]

Hi,
Try using Ereplace function as shown

Ereplace((Oconv(LinkName.DateColumnName, "DYMD[4,2,2]")),"-","")


Ketfos

[chulett]

DIGITS will work fine and will remove all non-numerics. Phillip just needs to get his conversion tweaked a little.

[phillip.small]

Hi,
Try using Ereplace function as shown

Ereplace((Oconv(LinkName.DateColumnName, "DYMD[4,2,2]")),"-","")


Ketfos

Thanks for showing me the Ereplace function but that did not work either.

[ketfos]

Hi,
You I agree with you.

I could not find the DIGITS function in Datastage 5.0. Is it correct?

I found Ereplace and Convert functions can help.

Ketfos

[leomauer]

My Oconv returns date like this 2004 08 20
Then this should work fine too:
Trim(Oconv(LinkName.DateColumnName, "DYMD[4,2,2]") , Space(1), "A")

But the question is: what is your input value look like?
Oconv input shoud be integer.

[phillip.small]

My Oconv returns date like this 2004 08 20
Then this should work fine too:
Trim(Oconv(Arg1, "DYMD[4,2,2]") , Space(1), "A")

But the question is: what is your input value look like?

The input is coming from character field like this:

20041030

[leomauer]

The input is coming from character field like this:

20041030

So what are you converting into what?
20041030 to 20041030?

[phillip.small]

My Oconv returns date like this 2004 08 20
Then this should work fine too:
Trim(Oconv(LinkName.DateColumnName, "DYMD[4,2,2]") , Space(1), "A")

But the question is: what is your input value look like?
Oconv input shoud be integer.

What does this "A" mean? This changed the format of the numbers.

[leomauer]

Trim (string,character [ ,option] )

option specifies the type of trim operation and can be one of the following:
L Removes leading occurrences of character.
T Removes trailing occurrences of character.
B Removes leading and trailing occurrences of character.
R Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
A Removes all occurrences of character.
F Removes leading spaces and tabs
E Removes trailing spaces and tabs
D Removes leading and trailing spaces and tabs, and reduces multiple spaces and tabs to single ones.

If option is not specified or is a null value, R is assumed

[chulett]

The input is coming from character field like this: 20041030

I'm a little confused as well. This seems to match your desired output format and would (usually) mean no conversion was required. So, what are we missing?

For what it's worth, DIGITS is a Built-In Transform that simplifies the call to OConv to strip the non-numerics:

Code: Select all

Oconv(%Arg1%, "MCN")

No clue if it was there in 5.x but I would think it would be.

[phillip.small]

The input is coming from character field like this: 20041030

I'm a little confused as well. This seems to match your desired output format and would (usually) mean no conversion was required. So, what are we missing?

For what it's worth, DIGITS is a Built-In Transform that simplifies the call to OConv to strip the non-numerics:

Code: Select all

Oconv(%Arg1%, "MCN")

No clue if it was there in 5.x but I would think it would be.

The output came out like this: 6811-01-06

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?