SQL*Loader and MS SQLServer bcp's milliseconds
Date: 30 Jan 2002 09:04:30 -0800
Message-ID: <9292180b.0201300904.7fa9c1bb_at_posting.google.com>
It's well known, that MS SQLServer bcp utility unloads dates with milliseconds which cannot be further properly processed by SQL*Loader.
MSSQL date looks like the following:
2002-01-30 16:44:36.780
The problem is that Oracle SQL*Loader cannot process ".780" in the previous example.
Two workarounds have been proposed for this problem in the newsgroup: 1) create a view in SQLServer DB which converts date fileds into default Oracle format (i.e. w/o milliseconds); 2) use special SQL*Loader commands like "TO_DATE" and "SUBSTR" in order to convert MSSQL format into Oracle format. None of these workarounds are simple, easily maintainable, etc.
I've found another workaround which is more simple. Just set NLS_DATE_FORMAT before loading via SQL*Loader to the following string:
SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS."000"
After that SQL*Loader works perfectly.
The test was done for Oracle 8.1.7.
Is that a correct workaround, and has anybody tried it before (the workaround seems like reinventing the weel) ?
Best Regards,
Aleksey Burdakov Received on Wed Jan 30 2002 - 18:04:30 CET