Oracle date columns, which include a time compenent, are not stored in a particular format. The formatting is done when you view the date, either by the current nls_date_format or by using the to_char function. When you are loading data, you need to use the to_date function in your SQL*Loader control file and include the format that the data in your text data file is in. In your example, you will need 'yyyymmdd' in your control file and 'yyyy-Mon-dd' either in your to_char or nls_date_format. Please see the examples below. The first example is for date only and the second is for a date that includes a time component.
scott@ORA92> -- create table with column of date datatype:
scott@ORA92> create table test_table (date_and_time date)
2 /
Table created.
scott@ORA92> -- contents of text data file test.dat:
20040312
scott@ORA92> -- contents of SQL*Loader control file test.ctl
scott@ORA92> -- that uses to_date function with format of data in control file:
LOAD DATA
INFILE 'test.dat'
REPLACE
INTO TABLE test_table
(date_and_time position (1:8) "to_date(:date_and_time,'yyyymmdd')")
scott@ORA92> -- load data into table test_table
scott@ORA92> -- from text data file test.dat
scott@ORA92> -- using SQL*Loader control file test.ctl:
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
scott@ORA92> -- view results:
scott@ORA92> select * from test_table
2 /
DATE_AND_
---------
12-MAR-04
scott@ORA92> -- view results with date column in desired format by using to_char function:
scott@ORA92> select to_char (date_and_time, 'yyyy-Mon-dd') from test_table
2 /
TO_CHAR(DAT
-----------
2004-Mar-12
scott@ORA92> -- change nls_date_format to desired format:
scott@ORA92> alter session set nls_date_format = 'yyyy-Mon-dd'
2 /
Session altered.
scott@ORA92> -- view results with date column in new nls_date_format without to_char:
scott@ORA92> select * from test_table
2 /
DATE_AND_TI
-----------
2004-Mar-12
The following additional example includes a time component.
scott@ORA92> -- create table with column of date datatype:
scott@ORA92> create table test_table (date_and_time date)
2 /
Table created.
scott@ORA92> -- contents of text data file test.dat:
20040312231136
scott@ORA92> -- contents of SQL*Loader control file test.ctl
scott@ORA92> -- that uses to_date function with format of data in control file:
LOAD DATA
INFILE 'test.dat'
REPLACE
INTO TABLE test_table
(date_and_time position (1:14) "to_date(:date_and_time,'yyyymmddhh24miss')")
scott@ORA92> -- load data into table test_table
scott@ORA92> -- from text data file test.dat
scott@ORA92> -- using SQL*Loader control file test.ctl:
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
scott@ORA92> -- view results:
scott@ORA92> select * from test_table
2 /
DATE_AND_
---------
12-MAR-04
scott@ORA92> -- view results with date column in desired format by using to_char function:
scott@ORA92> select to_char (date_and_time, 'yyyy-Mon-dd hh24:mi:ss') from test_table
2 /
TO_CHAR(DATE_AND_TIM
--------------------
2004-Mar-12 23:11:36
scott@ORA92> -- contents of text data file test.dat:
20040312
] 
Blog
Search
Help
Members
Register
Login
Home
