This discussion is archived
5 Replies Latest reply: Mar 27, 2013 5:46 AM by Mohamed Houri RSS

sqlldr dates loading help

carmac Newbie
Currently Being Moderated
Hi all,

I tried to load some dates using sqlldr. but those are loading to another dates.

Ex:

Input data file is:

01/01/1990
01/02/1992
02/03/1998
03/04/2000

loaded dates are:

01/01/2090
01/02/2092
02/03/2098
03/04/2000


1990 is converted to 2090.

in sqlldr i used the code for loading is

tmp_date date to_date(tmp_date,'MM/DD/YYYY')


Please help me

Thanks
  • 1. Re: sqlldr dates loading help
    jeneesh Guru
    Currently Being Moderated
    Can you paste your actual data (sample records) and actual control file?
  • 2. Re: sqlldr dates loading help
    carmac Newbie
    Currently Being Moderated
    Data file data is:

    01/01/1990
    01/02/1992
    02/03/1998
    03/04/2000

    Control file is

    LOAD DATA
    append
    into table temp
    trailing nulls
    (
    tmp_date date "to_date(tmp_date,'MM/DD/YYYY')"
    )
  • 3. Re: sqlldr dates loading help
    Karthick_Arp Guru
    Currently Being Moderated
    You need to change your control file. Following is my data and control file
    karthick% cat test_file.dat
    01/01/1990
    01/02/1992
    02/03/1998
    03/04/2000
    
    karthick% cat test_control.ctl
    load data
    infile *
    replace
    into table test_tbl
    (  
       tmp_date position(1:10) "to_date(:tmp_date,'MM/DD/YYYY')"
    )
    Now i execute sql loader
    karthick% sqlldr karthick/######@##### control=test_control.ctl data=test_file.dat
     
    SQL*Loader: Release 10.2.0.5.0 - Production on Wed Mar 27 07:48:35 2013
     
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.
     
    Commit point reached - logical record count 4
    and data in table
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
     
    SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
     
    Session altered.
     
    SQL> select * from test_tbl;
     
    TMP_DATE
    --------------------
    01-JAN-1990 00:00:00
    02-JAN-1992 00:00:00
    03-FEB-1998 00:00:00
    04-MAR-2000 00:00:00
     
    SQL> 
  • 4. Re: sqlldr dates loading help
    jeneesh Guru
    Currently Being Moderated
    carmac wrote:
    Data file data is:

    01/01/1990
    01/02/1992
    02/03/1998
    03/04/2000

    Control file is

    LOAD DATA
    append
    into table temp
    trailing nulls
    (
    tmp_date date "to_date(tmp_date,'MM/DD/YYYY')"
    )
    The control file syntax is wrong...
    D:\test>type test_data.txt
    01/01/1990
    01/02/1992
    02/03/1998
    03/04/2000
    D:\test>type test.ctl
    load data
    into table test
    fields terminated by ','
    (
    dt "to_date(:dt,'mm/dd/yyyy')"
    )
    
    
    
    D:\test>sqlldr scott/tiger data=D:\test\test_data.txt control=D:\test\test.ctl
    
    SQL*Loader: Release 11.2.0.1.0 - Production on Wed Mar 27 17:26:47 2013
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 3
    Commit point reached - logical record count 4
    
    
    SQL> select * from test;
    
    DT          C1
    ----------- ----------
    01-jan-1990
    02-jan-1992
    03-feb-1998
    04-mar-2000
    Edited by: jeneesh on Mar 27, 2013 5:30 PM
  • 5. Re: sqlldr dates loading help
    Mohamed Houri Pro
    Currently Being Moderated
    Here a simple example
    input.dat
    01/01/1990,
    01/02/1992,
    02/03/1998,
    03/04/2000,
    
    LOAD DATA 
    INFILE 'input.dat'
    append
    into table t1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    tmp_date date 'MM/DD/YYYY'
    )
    
    SQL> select * from t1;
    
    no rows selected
    
    SQL> desc t1;
               Name                            Null?    Type
               ------------------------------- -------- ------------------
        1      TMP_DATE                                 DATE
         
    C:\>sqlldr user/passwd@database control=ctl_file.ctl
    
    SQL*Loader: Release 10.2.0.3.0 - Production on Wed Mar 27 13:44:06 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 13
    Commit point reached - logical record count 14     
    
    SQL> alter session set nls_date_format='DD/MM/YYYY';
    
    Session altered.
    
    SQL> select * from t1;
    
    TMP_DATE
    ----------
    01/01/1990
    02/01/1992
    03/02/1998
    04/03/2000
    
    SQL> alter session set nls_date_format='DD-MON-YYYY';
    
    Session altered.
    
    SQL> select * from t1;
    
    TMP_DATE
    -----------
    01-JAN-1990
    02-JAN-1992
    03-FEB-1998
    04-MAR-2000
    
    {code}
    
    Best Regards
    Mohamed Houri
    www.hourim.wordpress.com                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points