Skip Characters in Oracle TO_DATE function

Question

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.

Answer 1

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