Page 1 of 1
DATE FORMAT
Posted: Thu May 11, 2006 1:51 pm
by adams06
I AM GETTING DATE IN THIS FORMAT
EX:
SOURCE IS ORAOCI9 STAGE
42606------------> MMDDYY
120105------------>MMDDYY
112805------------>MMDDYY
TARGET IS ORAOCI9
CAN ANY ONE TELL ME HOW TO FORMAT THE ABOVE INTO YYYYMMDD FORMAT.
THANKS IN ADVANCE
Posted: Thu May 11, 2006 2:00 pm
by phanee_k
Use the Iconv / OConv functions to convert the date into the date format that you need.
Posted: Thu May 11, 2006 2:03 pm
by DSguru2B
to be a little more specific, use
Code: Select all
OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]")
Re: DATE FORMAT
Posted: Thu May 11, 2006 2:04 pm
by gateleys
This has been discussed in many many postings in this forum. It would be nice if you clicked
here.
gateleys
HI!
Posted: Thu May 11, 2006 10:37 pm
by mdtauseefhussain
You can use TO_CHAR(COLUMN NAME,'YYYYMMDD') in the source query
date problem
Posted: Mon May 15, 2006 12:51 pm
by adams06
Hi
if i use this code i am getting the following error:
data for column exceeds column width (8), row 33 (approx), data = 2006/08/22
My Target column length is 8 and sql type is varchar.
DSguru2B wrote:to be a little more specific, use
Code: Select all
OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]")
Posted: Mon May 15, 2006 12:58 pm
by DSguru2B
My reply was based on the the input data in your original post.
Please tell me what is going to be your input data and what it should look like. Exactly.
Posted: Mon May 15, 2006 1:04 pm
by DSguru2B
try this
Code: Select all
DIGITS(OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]") )
Posted: Mon May 15, 2006 1:09 pm
by diamondabhi
so you wnat the length to be 8?
Posted: Mon May 15, 2006 1:10 pm
by diamondabhi
Then load the column into a stage variable and trim the special character and then load into the table.
Posted: Mon May 15, 2006 1:15 pm
by adams06
It worked.
Thank you
DSguru2B wrote:try this
Code: Select all
DIGITS(OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]") )