CODEWITHSUNDEEP
oracle-databasesql-insertdate-format
reactFullStackDeveloper
reactFullStackDeveloper

Reputation: 919

oracle Alter Session NLS FORMAT for bulk insert statements - not behaving as expected

I have exported out table information from an SQL DB in the format of insert statements. Many of the tables contain timestamp information the YYYY-MM-DD HH24:MI:SS format. Since there are hundreds of these statements it is not realistic for me to add the TO_DATE() statement with each date. I thought that altering the sessions NLS DATE format would resolve this issue, however I'm still getting an error about ORA-01843: not a valid month.

The columns data type is TIMESTAMP.

EXAMPLE:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' 

INSERT INTO gcGovernance (id, userID, grantAppID, grantAppUUID, 
grantCommCatID, grantApprovalCmnt, grantApprovalDate) VALUES (758, 163, 
408, 'iahfahfahashvai', 0, '', '2016-12-20 
14:32:17');

Upvotes: 2

Views: 726

Answers (2)

EdStevens
EdStevens

Reputation: 3872

If the receiving column is a TIMESTAMP, then you need to set NLS_TIMESTAMP_FORMAT, not NLS_DATE_FORMAT.

Upvotes: 6

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can try to use the format timestamp'YYYY-MM-DD HH24:MI:SS'

within that example insert statement such as

timestamp'2016-12-20 14:32:17' for grantApprovalDate column.

Demo

Upvotes: 0

Related Questions