Integer to Date conversion

senthilmp · Post by **senthilmp** » Fri Dec 12, 2008 9:18 am

Hi,

My source is having one of the 'Date Field' as an integer type , and i want this to be converted as Date type in the target.

eg: the source contains

20080325 i want this to be stored as date format in the target, can any one help out in this?

Regards,
Senthil MP

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Dec 12, 2008 9:25 am

You can work with it as a string input. What have you attempted so far?

chulett · Post by **chulett** » Fri Dec 12, 2008 9:46 am

Server job as marked or Parallel job per the forum you posted in?

srinivas.g · Post by **srinivas.g** » Sat Dec 13, 2008 1:01 am

througth iconv oconv function

oconv(iconv(columnname),D-DMY),D-DMY[2,2,4])

senthilmp · Post by **senthilmp** » Sat Dec 13, 2008 6:57 am

Hi, now its converting but still while inserting into the target table am getting the following error

"Attempt to convert String value "15-08-2005" to Date type unsuccessful"

I tried with both DATA and TIMESTAMP in target but i got this error

srinivas.g · Post by **srinivas.g** » Sat Dec 13, 2008 7:10 am

try this one
OCONV(ICONV(columnname,'D-MDY[2,2,4]'), 'D-YMD[4, 2, 2]')

datatype is timestamp

chulett · Post by **chulett** » Sat Dec 13, 2008 8:46 am

OK, Server job it seems. Rather than attack this piecemeal, how about details like target database & stage you are using? Custom or generated SQL? All affect the answer.

senthilmp · Post by **senthilmp** » Sat Dec 13, 2008 9:35 am

Hi all,

thanks for the response, i got it by searching DSXchange further,

the solution is have to use only ICONV and not the combination of OCONV & ICONV.

Gr8 Forum

chulett · Post by **chulett** » Sat Dec 13, 2008 10:13 am

So... DB2?

senthilmp · Post by **senthilmp** » Sat Dec 13, 2008 10:28 am

No its Oracle

chulett · Post by **chulett** » Sat Dec 13, 2008 11:09 am

In that case, using IConv only doesn't make much sense, but I'll take your word for it that it is actually working for you.