I’m trying to import some data including dates from a CSV file.
A row in the CSV file looks like this:
49988,FRO,27-Jan-15,26-Jan-15
I believe that’s what one might call the plain Oracle date format.
On the Specify Source Data Details page I find no pattern that matches that format. So I choose the closest that I see: “DMY “ with “-“ as Date Delimiter” and click next.
Yet when get to the Preview Results page, I’m disappointed.
Anybody know the right combo?
Thanks much,
Dan
–
Daniel B Madvig
Systems Analyst, Information Technology
651.631.5323
dbmadvig@unwsp.edu |
www.unwsp.edu
Karma (Justice) is getting what you deserve.
Mercy is not getting what you deserve.
Grace is getting what you do not deserve.
I believe that’s what one might call the plain Oracle date format.
In Oracle, date is controlled on 3 levels:
-
database,
-
instance
-
session.
You can query their values:
-
select * from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';
-
select * from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT';
-
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
They have influence in the mentioned order. So this means if you change your session parameters it will be final and let's say only important for you and your sql statements.
Session parameter is changed with command:
EXECUTE IMMEDIATE 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
where
yyyy-mm-dd hh24:mi:ss
is one of many Oracle date formats you would like to have.
If you cannot change session parameter in a way to run SQL command before, what seems to be your case, then it should be define "from outside". One of ways to achieve that in Windows-is registry. Look in Windows registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE*KEY_OraDb11g_home1*
where
KEY_OraDb11g_home1
is definition of your ORACLE_HOME (depend of your Oracle client installation).
Add/change NLS_DATE_FORMAT (SZ string value) to have desired date format. Example:
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE*YOUR_ORA_HOME*]
"NLS_DATE_FORMAT"="dd.mm.yyyy hh24:mi:ss"
After that restart Toad and that date will be default for all DATE actions, except Toad grid, whose value are additionally controlled through Toad options.
Keep in mind that registry influence all software on your workstation which use definition from that Oracle home.
I hope this helps.
Cheers
Damir
Damir, it’s not that complicated. You don’t need to adjust registry settings and Oracle nls parameters for this. It’s only the import settings that need to be correct.
Daniel, you need to choose “custom” as the date format, then in the dropdown under it, look for dd/mmm/yy.
Thanks, Damir, for your efforts.
And thanks, John, for the easier solution. That was not obvious to me, but it sure worked nicely.
Sent from my Black & Decker Multi-Function Toaster Oven
From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Thursday, January 29, 2015 7:20 AM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Importing dates
RE: Importing dates
Reply by John Dorlon
Damir, it’s not that complicated. You don’t need to adjust registry settings and Oracle nls parameters for this. It’s only the import settings that need to be correct.
Daniel, you need to choose “custom” as the date format, then in the dropdown under it, look for dd/mmm/yy.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
John, Toad is completely incapable of importing dates. The custom date format does nothing, no matter what you set it to or how you change the data it always produces error for dates.
Can you send me a few lines of your input file and your table DDL? This feature gets a lot of use, so I know it works, but maybe you found a bug…
Sure, it’s a really simple table with 2 dates in it:
CREATE TABLE ORDERGEN_DBA.IAN_INPUT_ACTVTY_NAME
(
INPUT_ACTVTY_CDE VARCHAR2(10 BYTE) NOT NULL,
INPUT_ACTVTY_DESC VARCHAR2(40 BYTE) NOT NULL,
ADD_DTTM DATE NOT NULL,
ADD_BY_USER VARCHAR2(30 BYTE) NOT NULL,
UPD_DTTM DATE NOT NULL,
UPD_BY_USER VARCHAR2(30 BYTE) NOT NULL
)
The input is a 1 row .csv file that I had exported from toad (I’m moving data from DEV to QA to Production)
INPUT_ACTVTY_CDE,INPUT_ACTVTY_DESC,ADD_DTTM,ADD_BY_USER,UPD_DTTM,UPD_BY_USER
ALL,Sales History & Dealer Inventory,04/29/2016 4:59:17 AM,VNDRATOSPD,04/29/2016 5:02:54 PM,WILSONMU
I just tested this with Toad 12.8 and it worked fine for me. If you are using a different version, please let me know and I will try with that too. These were my settings. Did you check “Four Digit Years” and Leading Zeros in Dates"? Sometimes I miss those. If your settings match but you are still getting errors, let me know what your settings are in Toad’s main options for date format, and also your windows settings for date format.
I have Toad 12.6.0.53. It does indeed work with those settings. For some reason I had been attempting to use the custom date format (of mm/dd/yyyy hh/mi/ss pm and mm/dd/yyyy with just the date) and was not able to get that to work.
I’m glad you got it working.
mm/dd/yyyy hh:nn:ss is what you would have needed to do this with a “custom date format”. I know nn isn’t intuitive for minutes, but there are some sample formats in the dropdown.
mm/dd/yyyy is working here, but maybe there is some other setting that is throwing things off.
I tried mm/dd/yyyy hh:nn:ss first as it was in the dropdown. When that didn’t work I tried mi instead of nn
I think I’ll just stick to the non-custom formats from now on 
