DSXchange

Hi All,

I am trying to populate string data in to timestamp.
my source data format is YYMMDDHHMMSSSS ,
i need to populate this data in to target table having timestamp YY-MM-DD HH:MM:SS.
To Achive this output i have been using this function

"OCONV(ICONV(TRIM(INPUT_COLUMN[1,8]),"D4YMD[4,2,2]"), "D-YMD[4,2,2]") : " " : OCONV(ICONV(TRIM(INPUT_COLUMN[9,2]:":":INPUT_COLUMN[11,2]:":":INPUT_COLUMN[13,2]), "MTS"), "MTS")".

But data in target table populating as null !
Does i need to change my code?

You can use the following function to convert the date into desired format DD[1,2]:'-':DD[3,2]:'-':DD[5,2]:' ': DD[7,2]: ':' :DD[9,2]: ':' :DD[11,2]

*Note: Smilies disabled in post - Content Editor*

Someone needs to learn about 'Disable Smilies'.

Sankar - Dump the output from your timestamp conversion and check what you actually get from it. Hint: is your incoming year two or four digits?

HI Guys,

Thanks for your reponse.

chulet,

My incoming year is 2 digits only.

Right... and how many characters from the string are you substringing out for the date piece?

Been a while since I player with Server but would "D4YMD[4,2,2]" say the delimiter is a "4"? Shouldn't it be DYMD[4,2,2]?

(Ignoring further posts identifying a two digit year... follow Craig Hulett's suggestion to figure out what should change)

HI Chulet,



12 characters i need to substring.
I tried with following code also.

"OCONV(ICONV(TRIM(INPUT_COLUMN[1,6]),"D4YMD[4,2,2]"), "D-YMD[4,2,2]") : " " : OCONV(ICONV(TRIM(INPUT_COLUMN[7,2]:":":INPUT_COLUMN[9,2]:":":INPUT_COLUMN[11,2]), "MTS"), "MTS")".

But it is not working.

ICONV(TRIM(INPUT_COLUMN[1,6]),"D4YMD[4,2,2]") is one problem. Now you are pulling 6 but telling IConv it is 8 all together. And Kryt0n already pointed out your delimiter issue. Try this instead:

ICONV(INPUT_COLUMN[1,6],"DYMD[2,2,2]")

Iconv() is clever enough not to need format information.