Home » SQL & PL/SQL » SQL & PL/SQL » Loading date field with CCYYMMDD format using SQL*Loader
Loading date field with CCYYMMDD format using SQL*Loader [message #129977] Wed, 27 July 2005 09:08 Go to next message
dhaval_khamar
Messages: 4
Registered: June 2005
Location: Bangalore
Junior Member

Hi,

Can anybody give way to load date field in 'CCYYMMDD' format, using SQL*Loader.

I tried it, but all times it gives error "ORA-01820: format code cannot appear in date input format"

Thanks in advance.
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129979 is a reply to message #129977] Wed, 27 July 2005 09:22 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Use YYYY instead of CCYY

hth
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129981 is a reply to message #129977] Wed, 27 July 2005 09:48 Go to previous messageGo to next message
dhaval_khamar
Messages: 4
Registered: June 2005
Location: Bangalore
Junior Member

We can not use YY instead of CC.
Since 15-JUL-2005 (DD-MM-YYYY), is not same as 20050715 (CCYYMMDD), but 21050715 (CCYYMMDD).

I guess we do not have any solution other then dump numeric data into some column; and then using some procedure manipulate the data to convert into desired date format.


Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129983 is a reply to message #129981] Wed, 27 July 2005 09:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the DDL of table,a sample data and control file.
We can give a try.
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129987 is a reply to message #129981] Wed, 27 July 2005 10:15 Go to previous messageGo to next message
Art Metzer
Messages: 2477
Registered: December 2002
Senior Member
I have no problems.

Creating the table in SQL*Plus:
SQL> CREATE TABLE t (
  2      id      NUMBER
  3  ,   text    VARCHAR2(4)
  4  ,   dt      DATE
  5  )
  6  /
 
Table created.
 
SQL>

The sample data file:
$ cat sampledata.txt
1,QRS,20050101
2,TUV,20050715
3,WXY,21050715
4,ZAB,20040801
$

The SQL*Loader control file:
$ cat sample.ctl
LOAD DATA
INFILE 'sampledata.txt'
TRUNCATE
INTO TABLE scott.t
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS (
    id      NULLIF id=BLANKS     "TRIM(:id)"
,   text    NULLIF text=BLANKS   "TRIM(:text)"
,   dt      DATE "YYYYMMDD"      "TRIM(:dt)"
)
$

The SQL*Loader call:
$ sqlldr scott/tiger control=sample.ctl

SQL*Loader: Release 8.0.6.3.0 - Production on Wed Jul 27 11:03:43 2005

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
$

And finally, a SELECT from SQL*Plus proving that the DATEs were loaded successfully:
SQL> SELECT t.id
  2  ,      t.text
  3  ,      TO_CHAR(t.dt
  4         ,       'fmDy DD-MON-YYYY HH:fmMI:SS AM') stored_date
  5  FROM   t
  6  /
  
        ID TEXT STORED_DATE
---------- ---- ---------------------------
         1 QRS  Sat 1-JAN-2005 12:00:00 AM
         2 TUV  Fri 15-JUL-2005 12:00:00 AM
         3 WXY  Wed 15-JUL-2105 12:00:00 AM
         4 ZAB  Sun 1-AUG-2004 12:00:00 AM
 
SQL>
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #130493 is a reply to message #129977] Sun, 31 July 2005 09:47 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
21050715 is a strange way to note the date 15 july 2005.
You could subtract 100 years from the resulted date when using YYYY.

hth
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #132748 is a reply to message #130493] Mon, 15 August 2005 23:52 Go to previous messageGo to next message
dhaval_khamar
Messages: 4
Registered: June 2005
Location: Bangalore
Junior Member

Hi Guyz,

Thanks for all replies. I got a very easy solution....Use RRYYMMDD format, since CCYYMMDD format is nly to read records.


$ cat sample.ctl
LOAD DATA
INFILE 'sampledata.txt'
TRUNCATE
INTO TABLE scott.t
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS (
id NULLIF id=BLANKS "TRIM(:id)"
, text NULLIF text=BLANKS "TRIM(:text)"
, dt DATE "RRYYMMDD" "TRIM(:dt)"
)


If you see the results posted by Art Metzer, for 21050715, the year created is 2105, what we want is 2005. and this could be achived by above control file.


Regards,
Dhaval Khamar
Re: Loading date field with CCYYMMDD format using SQL*Loader [message #132864 is a reply to message #132748] Tue, 16 August 2005 08:08 Go to previous message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
What is the logic in defining the year 2005 as 2105? I really want to know what kind of person (manager?) decided to force their twisted lack of knowledge onto others.

And what happens when you really want the year 2105? Rewrite all your code?
Previous Topic: Convert Rows to Columns
Next Topic: dynamic order by and decode and order by (merged threads)
Goto Forum:
  


Current Time: Sat Aug 23 15:37:17 CDT 2014

Total time taken to generate the page: 0.12268 seconds