Date help

paddu · Post by **paddu** » Wed Mar 14, 2007 4:12 pm

I have dates coming from the source Flat file as

Varchar  11022006  (MMDDYYYY)

I have to load this into oracle as MM/DD/YYYY .

I acheived this by using
FMT(src_out_AMA_LICENSE.Raw_St_Lic_expr_dt,'R##/##/####') .It loads as MM/DD/YYYY in varchar field in oracle.

how do i convert MMDDYYYY into MM/DD/YYYY when loading into Date datatype field in oracle?

Thanks
Paddu

DSguru2B · Post by **DSguru2B** » Wed Mar 14, 2007 4:18 pm

Just specify that field as Date. You will notice in the "Generated SQL" that OCI stage puts a TO_DATE() function to convert the string to date.

paddu · Post by **paddu** » Wed Mar 14, 2007 4:20 pm

so we don't have to use any date conversions ????.
just loading string to date would resolve ? let me try.

i will update you quickly.

Thanks
paddu

paddu · Post by **paddu** » Wed Mar 14, 2007 4:30 pm

No, it did not work

For a date from source which is 12312006 i got 2/14/5677 as the result in oracle.

DSguru2B · Post by **DSguru2B** » Wed Mar 14, 2007 4:34 pm

I think you need the date to be in YYYY-MM-DD format. Try this

Code: Select all

in.Col[5,4]:"-":in.Col[1,2]:"-":in.Col[3,2]

Also, what is the data type and length of the date field in Oracle? Is it 38? Do you require time part as well? In other words, is it really a date field or timestamp field.

paddu · Post by **paddu** » Wed Mar 14, 2007 4:47 pm

i first added Date of length 10 and loaded into oracle and it gave those values which i mentioned earlier ,

Now i changed the field as Timestamp length 38 while loading into oracle ( which we actually want ) . datastage threw an error as DBMS.CODE=ORA-01861 literal does not match format string

kumar_s · Post by **kumar_s** » Wed Mar 14, 2007 4:54 pm

What To_DATE function you used on Oracle?
Try using

Code: Select all

TO_DATE(:1,'MMDDYYYY')

. Make use use the field as varchar till the target.

DSguru2B · Post by **DSguru2B** » Wed Mar 14, 2007 4:54 pm

With timestamp you also need to concatenate "00:00:00".

us1aslam1us · Post by **us1aslam1us** » Wed Mar 14, 2007 4:54 pm

Leave the length to 10 and try this:

Code: Select all

OCONV(ICONV(src_out_AMA_LICENSE.Raw_St_Lic_expr_dt,"DMDY"),"D/MDY[2,2,4]")

paddu · Post by **paddu** » Wed Mar 14, 2007 5:00 pm

us1aslam1us

i tried your solution
it ended as blank in oracle.

i even tried

OCONV(ICONV(src_out_AMA_LICENSE.Raw_St_Lic_expr_dt,'D4MDY'),'D4/DMY[2,2,4]')

even ended as blank in oracle.

kumar_s · Post by **kumar_s** » Wed Mar 14, 2007 5:01 pm

Oracle Date is Timestamp.
No need to convert it specially.

paddu · Post by **paddu** » Wed Mar 14, 2007 5:04 pm

kumar,

on oracle side my field is Date , front side in datastage it looks like timestamp 38 when i imported the table definition from oracle .

paddu · Post by **paddu** » Wed Mar 14, 2007 5:08 pm

As i mentioned before my source date format is MMDDYYYY . In my target oracle we want the date to be like MM/DD/YYYY

kumar_s · Post by **kumar_s** » Wed Mar 14, 2007 5:10 pm

Ok, so its the field at the level of micro second.
You can use TO_TIMESTAMP(). But still your source has only date part in it. So it dosen't make much difference.

kumar_s · Post by **kumar_s** » Wed Mar 14, 2007 5:11 pm

You need not define delimiter to Date in oracle. You just need to convert it into Oracle internal format thats it.