1 Reply Latest reply on Mar 4, 2013 6:40 PM by L-MachineGun

    SQL Loader Date Problems

      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:
      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.

        • 1. Re: SQL Loader Date Problems
          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.