This discussion is archived
1 Reply Latest reply: Mar 4, 2013 10:40 AM by L-MachineGun RSS

SQL Loader Date Problems

Shaps Newbie
Currently Being Moderated
Hi Guys,

We have a SQL Loader script that used to data in the 'mm/dd/yyyy' format. Post migration of the script onto a seprate server the format is getting stored in 'dd/mm/yyyy' format in a VARCHAR datatype column for DOB.

Please find below the Loader script :
 "insert into tsa_lists values ('" & ttsa_list_typ & "','" & ttsa_list_num & "',"
                                    if ttsa_list_typ <> "AuthRep" then 
                                    
                                    inscmd.CommandText= "insert into tsa_lists values ('" & ttsa_list_typ & "','" & ttsa_list_num & "',?,?,?,?,?,?,?,?,?,?,sysdate,?,?)"
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_sid",200,1,30,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_lastname",200,1,500,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_firstname",200,1,500,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_firstletter",200,1,1,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_middlename",200,1,500,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_dob",200,1,100,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_pob",200,1,200,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_citizenship",200,1,200,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_cleared",200,1,3,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_misc",200,1,2000,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_firstname_orig",200,1,500,"")
                                    inscmd.Parameters.Append inscmd.CreateParameter("ttsa_lastname_orig",200,1,500,"")
Data coming in the below format:
SID     CLEARED     LASTNAME     FIRSTNAME     MIDDLENAME     TYPE     DOB     POB     CITIZENSHIP     PASSPORT/IDNUMBER     MISC     
3799509          A     ABD AL SALAM MARUF ABDALLAH               01-Jul-55                         
3799512          A     ABD AL SALAM MARUF ABDALLAH               01-Jan-80                         
3727959          A     KHALID KHALIL IBRAHIM MAHDI               11-Nov-50                         
3458238          A     KHALID KHALIL IBRAHIM MAHDI               08-Jan-81                         
3458242          A     KHALID KHALIL IBRAHIM MAHDI               31-Jul-81                         
3458231          A     KHALID KHALIL IBRAHIM MAHDI               01-Aug-81                         
2407275          A     MUSA BARARUDDIN Y DAGAM               19-Aug-62                         

Please can you guys suggest a way.

Cheers,
Shazin
  • 1. Re: SQL Loader Date Problems
    L-MachineGun Pro
    Currently Being Moderated
    1) That does not look like anything recognized by "SQL*Loader" utility.

    2) If you just really mean a "sql" statement to load data...then:

    a) If table column is date type, then use the TO_DATE() function
    b) If column is Varchar2 you should not save date's in a varchar2 column.

    In any case check out the NLS_DATE_FORMAT parameter on the database and or the same parameter on client side.
    :p

Legend

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