3

I'm importing data that has SQL Server formatted dates in a .tsv (yyyy-mm-dd hh24:mi:ss.mmm) into an Oracle database using SQL Developer's Import Data wizard.

How can I ignore the .mmm characters for importing them into a DATE column? I cannot seem to find an answer to this; I get that DATE columns don't hold milliseconds, but why can't I ignore specific patterns in the TO_DATE call?

Also note that because I'm not generating the TO_DATE calls, I cannot SUBSTRING or otherwise manipulate the .tsv's value during the import.

Import Data Wizard - Date

Ehryk
  • 1,930
  • 2
  • 27
  • 47

2 Answers2

5

You can use the pound sign (#) for this:

SELECT TO_DATE('2015-01-01 01:00:00.999', 'yyyy-mm-dd hh24:mi:ss.###') FROM dual;
--> 01/01/2015 01:00:00

I did not find this in the documentation, so I can't say why, but these also work:

SELECT TO_DATE('01_','hh24#') FROM dual;

SELECT TO_DATE('01:01:01.0xx','hh24:mi:ss.###') FROM dual;

These don't seem to work:

SELECT TO_DATE('010','hh24#') FROM dual;

SELECT TO_DATE('01:01:01.xxx','hh24:mi:ss.###') FROM dual;

This seems to be undocumented, but you seem to be able to interchange the punctuations without problems:

SELECT TO_DATE('2015-01;01 11:12/13',
               'yyyy.mm,dd_hh-mi ss') FROM dual;
--> 01/01/2015 11:12:13

You can use Character literals, enclosed in double quotation marks if you know which text to ignore:

SELECT TO_DATE('foo2015bar-01-!#%}01', '"foo"yyyy"bar"-mm-"!#%}"dd') FROM dual;
--> 01/01/2015
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
  • This is exactly what I'm looking for. Does it stand in for any character, including `.` and `:`? Something like `TO_DATE('junk2015.a03.b27c13:55d.33.888efg', '####yyyy.#mm.#dd#hh24.mi#.ss#######')` – Ehryk Mar 25 '15 at 15:23
  • I did not find it in the documentation, but it does not seem to work like this. `('01_','hh24#')` works, while `('010','hh24#')` doesn't. `('01:01:01.0xx','hh24:mi.ss.###')` works, but `('01:01:01.xxx','hh24:mi.ss.###')` doesn't. – Peter Lang Mar 25 '15 at 15:45
  • So it has to start with a number? Hmm. Note the `.` in the format string between mi and ss working for `:`. – Ehryk Mar 25 '15 at 15:54
  • This worked, but I'm still confused as to how the `TO_DATE` is working with these characters: `#:.` (it also works with `SELECT TO_DATE('2015-01-01 01:00:00.999', 'yyyy.mm.dd hh24.mi:ss....') FROM dual;`. Could you add some detail on how this works? – Ehryk Mar 27 '15 at 06:35
  • I tried to edit my findings into my answer, but I could not find it in the documentation. Feel free to ask another question, linking to this one - maybe someone else knows how this works in detail. – Peter Lang Mar 27 '15 at 07:08
  • Thanks for the additional detail! – Ehryk Mar 27 '15 at 07:09
1

There's no need to do this; Oracle will automagically convert a timestamp into a date if you attempt to add fractional seconds to a date column. Use TO_TIMESTAMP() instead and embrace the fractional seconds.

SQL> create table tmp_test (dt date);

Table created.

SQL> insert into tmp_test
  2  select to_timestamp('2015-03-24 13:10:03.654','yyyy-mm-dd hh24:mi:ss.FF3')
  3    from dual;

1 row created.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.FF3';

Session altered.

SQL> select * from tmp_test;

DT
-------------------
2015-03-24 13:10:03

SQL>
Ben
  • 51,770
  • 36
  • 127
  • 149
  • I appreciate that there are workarounds for it; but in this case I'm using the Import Data wizard in SQL Developer, the destination column is a `DATE` and all I have to modify is the format string of `TO_DATE()` only, not `TO_TIMESTAMP()`. What I'm looking for is any notation that will ignore the character at that position: for example `yyyy-mm-dd hh24:mi:ss****` or something that will effectively ignore the milliseconds with a `TO_DATE()`. – Ehryk Mar 24 '15 at 15:13