Page 1 of 1
Integer to Date conversion
Posted: Fri Dec 12, 2008 9:18 am
by senthilmp
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
Posted: Fri Dec 12, 2008 9:25 am
by Sainath.Srinivasan
You can work with it as a string input. What have you attempted so far?
Posted: Fri Dec 12, 2008 9:46 am
by chulett
Server job as marked or Parallel job per the forum you posted in?
Posted: Sat Dec 13, 2008 1:01 am
by srinivas.g
througth iconv oconv function
oconv(iconv(columnname),D-DMY),D-DMY[2,2,4])
Posted: Sat Dec 13, 2008 6:57 am
by senthilmp
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
Posted: Sat Dec 13, 2008 7:10 am
by srinivas.g
try this one
OCONV(ICONV(columnname,'D-MDY[2,2,4]'), 'D-YMD[4, 2, 2]')
datatype is timestamp
Posted: Sat Dec 13, 2008 8:46 am
by chulett
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.
Posted: Sat Dec 13, 2008 9:35 am
by senthilmp
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
Posted: Sat Dec 13, 2008 10:13 am
by chulett
So... DB2?
Posted: Sat Dec 13, 2008 10:28 am
by senthilmp
No its Oracle
Posted: Sat Dec 13, 2008 11:09 am
by chulett
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.