Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett , rschirm , roy
paddu
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
Post
by paddu » Wed Mar 14, 2007 4:12 pm
I have dates coming from the source Flat file as
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
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
paddu
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
paddu
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Charter Member
Posts: 5245 Joined: Thu Jun 16, 2005 11:00 pm
Post
by kumar_s » Wed Mar 14, 2007 4:54 pm
What To_DATE function you used on Oracle?
Try using
. Make use use the field as varchar till the target.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
DSguru2B
Charter Member
Posts: 6854 Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX
Post
by DSguru2B » Wed Mar 14, 2007 4:54 pm
With timestamp you also need to concatenate "00:00:00".
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
us1aslam1us
Charter Member
Posts: 822 Joined: Sat Sep 17, 2005 5:25 pm
Location: USA
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]")
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
paddu
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Charter Member
Posts: 5245 Joined: Thu Jun 16, 2005 11:00 pm
Post
by kumar_s » Wed Mar 14, 2007 5:01 pm
Oracle Date is Timestamp.
No need to convert it specially.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
paddu
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Premium Member
Posts: 232 Joined: Tue Feb 22, 2005 11:14 am
Location: California
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
Charter Member
Posts: 5245 Joined: Thu Jun 16, 2005 11:00 pm
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
kumar_s
Charter Member
Posts: 5245 Joined: Thu Jun 16, 2005 11:00 pm
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'