Oracle TO_DATE函数中的跳过字符 [英] Skip Characters in Oracle TO_DATE function

问题描述

我正在使用SQL Developer的导入数据"向导将.tsv(yyyy-mm-dd hh24:mi:ss.mmm)中具有SQL Server格式化日期的数据导入Oracle数据库.

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.

如何将.mmm字符导入DATE列而忽略?我似乎找不到答案.我知道DATE列不包含毫秒，但是为什么不能忽略TO_DATE调用中的特定模式?

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?

还请注意，由于我没有生成TO_DATE调用，因此在导入期间无法SUBSTRING或以其他方式操纵.tsv的值.

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.

推荐答案

您可以为此使用井号(#):

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;

这些似乎无效:

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

---

如果您知道要忽略哪些文本，则可以使用用双引号括起来的字符文字:

SELECT TO_DATE('foo2015bar-01-!#%}01', '"foo"yyyy"bar"-mm-"!#%}"dd') FROM dual;
--> 01/01/2015

这篇关于Oracle TO_DATE函数中的跳过字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！