Home » RDBMS Server » Server Utilities » Converting Date data in SQL Loader
Converting Date data in SQL Loader [message #368271] Tue, 24 October 2000 15:11 Go to next message
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 Go to previous message
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.
Previous Topic: Urgent : UTL_FILE - Error
Next Topic: Export Error EXP-00003
Goto Forum:
  


Current Time: Thu Mar 28 10:26:31 CDT 2024