DATE FORMAT

[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

[phanee_k]

Use the Iconv / OConv functions to convert the date into the date format that you need.

[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]")

[gateleys]

This has been discussed in many many postings in this forum. It would be nice if you clicked here.

gateleys

[mdtauseefhussain]

You can use TO_CHAR(COLUMN NAME,'YYYYMMDD') in the source query

[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.

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]")

[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.

[DSguru2B]

try this

Code: Select all

DIGITS(OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]") )

[diamondabhi]

so you wnat the length to be 8?

[diamondabhi]

Then load the column into a stage variable and trim the special character and then load into the table.

[adams06]

It worked.

Thank you

try this

Code: Select all

DIGITS(OCONV(ICONV(FMT(In.date,"6'0'R"),"DMDY[1,1,2]"),"DYMD[4,2,2]") )