使用 SQL*Loader 控制文件将日期从一种格式转换为另一种格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3308518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert date from one format to another using SQL*Loader control file
提问by Alex Larzelere
The data from the infile is in the format MM/DD/YYYYhow do I tell the control file to load it into the database as YYYYMM?
来自 infile 的数据的格式MM/DD/YYYY如何告诉控制文件将其加载到数据库中YYYYMM？
回答by APC
When you specify the columns in the INFILE declare just identify the format the data is held in. Like this
当您在 INFILE 声明中指定列时，只需确定数据保存的格式。像这样
load data
infile 'whatever.csv'
into table t23
fields terminated by ','
trailing nullcols
(
col_1 integer
, col_2 char
, col_3 date "MM/DD/YYYY"
, col_4 date "MM/DD/YYYY"
, col_5 char
)
Don't worry about the "to" date format. That is only for display purposes. Oracle stores dates in its own internal representation.
不要担心“到”日期格式。这仅用于显示目的。Oracle 将日期存储在它自己的内部表示中。
回答by Adam Musch
Are you trying to load the MM/DD/YYYY data into a char/varchar2 field, or into a date field?
您是否尝试将 MM/DD/YYYY 数据加载到 char/varchar2 字段或日期字段中？
If you're trying to load it into a date field and you want to preserve the day of the month, APC's answer is correct. You can always just present the YYYYMM if that's what you want to do.
如果您尝试将其加载到日期字段中并希望保留月份中的日期，则 APC 的答案是正确的。如果这是您想要做的，您始终可以只显示 YYYYMM。
If you're trying to load it into a date field and you want to truncate it to the first day of the month, I think something like this would work:
如果您尝试将其加载到日期字段中，并且想将其截断到本月的第一天，我认为这样的操作会起作用：
date_column date "MM/DD/YYYY" "trunc(:date_column, 'mm')"
date_column date "MM/DD/YYYY" "trunc(:date_column, 'mm')"
If inserting into a CHAR/VARCHAR2 column, you'd could to transform it a little differently:
如果插入到 CHAR/VARCHAR2 列中，您可以稍微改变一下：
vc2_column char "substr(:vc2_column, 7, 4) || substr(:vc2_column, 1, 2)"
vc2_column char "substr(:vc2_column, 7, 4) || substr(:vc2_column, 1, 2)"
