OraFAQ Forum: SQL & PL/SQL » Loading date field with CCYYMMDD format using SQL*Loader

Home » SQL & PL/SQL » SQL & PL/SQL » Loading date field with CCYYMMDD format using SQL*Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loading date field with CCYYMMDD format using SQL*Loader [message #129977]

Wed, 27 July 2005 09:08

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.

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129979 is a reply to message #129977]

Wed, 27 July 2005 09:22

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use YYYY instead of CCYY

hth

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129981 is a reply to message #129977]

Wed, 27 July 2005 09:48

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.

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129983 is a reply to message #129981]

Wed, 27 July 2005 09:58

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #129987 is a reply to message #129981]

Wed, 27 July 2005 10:15

Art Metzer
Messages: 2480
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>

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #130493 is a reply to message #129977]

Sun, 31 July 2005 09:47

Frank
Messages: 7901
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

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #132748 is a reply to message #130493]

Mon, 15 August 2005 23:52

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

Report message to a moderator

Re: Loading date field with CCYYMMDD format using SQL*Loader [message #132864 is a reply to message #132748]

Tue, 16 August 2005 08:08

joy_division
Messages: 4963
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?

Report message to a moderator

Previous Topic:	Convert Rows to Columns
Next Topic:	dynamic order by and decode and order by (merged threads)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 01 03:45:57 CST 2026