SQLLDR CTL:将DDMONYYYY中接收到的格式为YYYYMM或MM / DD / YYYY的数据库字段加载日期字段 [英] SQLLDR CTL: Load date field received in DDMonYYYY to db fields with formats of YYYYMM or MM/DD/YYYY
问题描述
我假设您将数据放入varchar2列,因此控件文件中的行应该看起来像这样其中数据正在被操纵以改变格式。首先将其转换为日期,然后使用to_char格式化:
,DATE_RX_WRITTEN CHARto_char(to_date(:DATE_RX_WRITTEN, DYYYYYY),YYYYMM)
或
_ pre>
,DATE_RX_WRITTEN CHARto_char(to_date(:DATE_RX_WRITTEN,'DDMONYYYY'),'MM / DD / YYYY')
/ pre> 如果您能够考虑在表中将其作为DATE数据类型,并将其转换为方式,那么您可以根据需要进行转换您选择它:
,DATE_RX_WRITTEN DATEDDMONYYYY
I have source date data in the format of DDMonYYYY (e.g. 25Jan2014). I am using sqlldr to load the data into various fields of two different formats (1) YYYYMM and (2) MM/DD/YYYY. How do I accomplish this? Thanks.
I assume you are putting data into a varchar2 column, so the lines in the control file should look something like this where the data is being manipulated on the way in to change the formatting. First convert it to a date, then use to_char to format it:
,DATE_RX_WRITTEN CHAR "to_char(to_date(:DATE_RX_WRITTEN, 'DDMONYYYY'), 'YYYYMM')"
or
,DATE_RX_WRITTEN CHAR "to_char(to_date(:DATE_RX_WRITTEN, 'DDMONYYYY'), 'MM/DD/YYYY')"
If you are able to, consider making it a DATE datatype in the table instead and convert it on the way in. That way you can convert it as needed when you select it instead:
,DATE_RX_WRITTEN DATE "DDMONYYYY"
这篇关于SQLLDR CTL:将DDMONYYYY中接收到的格式为YYYYMM或MM / DD / YYYY的数据库字段加载日期字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!