| Converting Date data in SQL Loader [message #368271] |
Tue, 24 October 2000 15:11  |
Shanthi
Messages: 8
Registered: October 2000
|
Junior Member |
|
|
Hi,
I am using SQL Loader to load data into Oracle Database. Data files are generated from SYBASE database.
Now my problem is SYBASE has a datetime field which if of format ('mon dd yyyy hh:mi:ss:mmmAM')
, i.e Sybase stores millseconds too. When moving this data into Oracle I need to trunc/eliminate millseconds and this is where I need help.
I am unable to do this as my flat files are variable length delimited files.
You might suggest that I generate flatfiles with millseconds truncated, unfortunately, SYBASE does not have a format function which returns all of the date except millseconds.
Can someone help me.
Thanks in advance.
|
|
|
|
| Re: Converting Date data in SQL Loader [message #368272 is a reply to message #368271] |
Tue, 24 October 2000 18:27  |
Thaniks
Messages: 8
Registered: October 2000
|
Junior Member |
|
|
Hi,
The replace command will eliminate the millisecond in the data.
replace(:date_column,substr(:date_column,-6,4),'')
Please try with the following in the loader script
date_column "to_char(to_date(replace(:date_column,substr(:date_column,-6,4),''),'MON DD YYYY hh:mi:ssAM'), 'DD-MON-YYYY hh:mi:ssAM')"
Thaniks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
