SQL TO_DATE - for every single date field?

Question

After years of using MySQL, having to move a table over to Oracle SQL (am using SQL Developer). Created the table and now just wanted to check it with a single INSERT statement, getting this:

INSERT INTO table_name 
VALUES ('1001','LAWRENCE-INDIANAPOLIS','01-06-02','I8112NP','05DX8105408','2013-06-03','2016-03-11','2018-04-29','2038-01-01','yes','yes','yes','2012-10-25','CCE','7360D33','R8NR6N0','70F63951959F9','2016-03-11')

Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).      
If the "FX" modifier has been toggled on, the literal must match exactly,
       with no extra whitespace.
*Action:   Correct the format string to match the literal.

Do I really have to put a TO_DATE format in front of every single DATE field in the INSERT statement, even though they're already in the proper YYYY-MM-DD format?

yes you should if the datatype of those columns is `date` or use `date '2013-06-03'` ... — Vamsi Prabhala, Apr 30 '18 at 16:52
My fault - only just learned from a co-worker that Oracle DATE format is not the same as MySQL DATE format. Will probably be easier to parse the data selected from the MySQL table and format it for Oracle SQL before putting into an INSERT statement. — BigRedEO, Apr 30 '18 at 16:59
If you defined your table correctly, meaning that the date column is in the `date` data type, then you are in luck: you can change your `NLS_DATE_FORMAT` setting. But I see a problem: in your attempted code, you have dates in two formats... what does '01-06-02' even mean? Which is the year, which is the month? You need all your "incoming" columns to use the same format, for example `'2013-06-03'`; otherwise you **will** need to use `to_date` only on the columns that are not in "standardized" format. Another question though... can't you migrate date **directly** to Oracle `date` data type? — , Apr 30 '18 at 17:28

score 3 · Answer 1 · answered Apr 30 '18 at 17:04

Well, you don't have to do it every time, but good practice says that you should instruct Oracle what you have and what you expect of it to do.

It is about NLS settings. If date format is different from one you use, you'll get an error (as you already know it):

SQL> create table test (datum date);

Table created.

SQL> insert into test values ('2013-06-03');
insert into test values ('2013-06-03')
                         *
ERROR at line 1:
ORA-01861: literal does not match format string

But, if you modify date format so that it matches format you use, everything will be OK:

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

Session altered.

SQL> insert into test values ('2013-06-03');

1 row created.

SQL>

Another option, which doesn't depend on NLS settings, is to use DATE literal. It is always in format YYYY-MM-DD and has to be preceded by the DATE keyword:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> insert into test values ('2013-06-03');
insert into test values ('2013-06-03')
                         *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> insert into test values (date '2013-06-03');

1 row created.

SQL>

Or, as you found out, use TO_DATE function with the appropriate format mask.

Date literal - for copying data from an existing table (migration from another RDBMS to Oracle)? I don't think so. — , Apr 30 '18 at 17:25
Those values (the OP posted) already are in YYYY-MM-DD format, are enclosed into single quotes and all that is missing is the DATE (key)word. I don't kniw, never migrated anything from MySQL, but that doesn't look like a major issue (adding DATE). Though, I might be wrong. — Littlefoot, Apr 30 '18 at 17:32
If there are millions - or even thousands! - of rows to migrate it might be annoying. I think the NLS settings option is probably the better one. — David Faber, Apr 30 '18 at 18:18
"Annoying" is not the issue. If the dates already exist in a column (perhaps in string data type), you **can't** simply add the keyword `DATE` in front of them. This is not even a MySQL issue, it is also internal to Oracle. The `DATE` literal only accepts a string **literal**, you can't write `date col1` where col1 is a varchar2 column in format 'yyyy-mm-dd' - that use of the `DATE` keyword is **not valid** in Oracle. — , Apr 30 '18 at 18:30

score 1 · Answer 2 · answered Apr 30 '18 at 17:03

1

In Oracle, use the date keyword:

INSERT INTO table_name 
    VALUES ('1001', 'LAWRENCE-INDIANAPOLIS', '01-06-02', 'I8112NP', '05DX8105408',
            DATE '2013-06-03', DATE '2016-03-11', DATE '2018-04-29', DATE '2038-01-01', 'yes', 'yes', 'yes', DATE '2012-10-25', 'CCE', '7360D33', 'R8NR6N0', '70F63951959F9', DATE '2016-03-11'
           );

answered Apr 30 '18 at 17:03

Gordon Linoff

1,242,037
58
646
786

On existing data, which is being migrated from another RDBMS? I don't think so. – Apr 30 '18 at 17:24

score 0 · Answer 3 · answered Apr 30 '18 at 17:41

The default DATE format used depends on the setting of the NLS_DATE_FORMAT environment var. To find out what this is, use the following query:

SELECT *
  FROM V$NLS_PARAMETERS
  ORDER BY PARAMETER;

This will dump all of the NLS parameters as they are currently set in your database. As an example, in the database I'm working in right now the query above returns:

NLS_CALENDAR               GREGORIAN
NLS_CHARACTERSET           US7ASCII
NLS_COMP                   BINARY
NLS_CURRENCY               $
NLS_DATE_FORMAT            DD-MON-RR
NLS_DATE_LANGUAGE          AMERICAN
NLS_DUAL_CURRENCY          $
NLS_ISO_CURRENCY           AMERICA
NLS_LANGUAGE               AMERICAN
NLS_LENGTH_SEMANTICS       BYTE
NLS_NCHAR_CHARACTERSET     AL16UTF16
NLS_NCHAR_CONV_EXCP        FALSE
NLS_NUMERIC_CHARACTERS     .,
NLS_SORT                   BINARY
NLS_TERRITORY              AMERICA
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT            HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT         HH.MI.SSXFF AM TZR

So on my system, if I wanted to enter a date-as-character string and have Oracle translate it correctly I'd have to enter is as e.g. 24-Apr-18.

SQL TO_DATE - for every single date field?

3 Answers3