title: Oracle String to Date conversion in java application
body: <p>I ran into a weird situation which I am unable to explain why. I need your help to understand.
  Here is my story. I had created two identical tables, one for latest information for a person and the other for historical information. Only one record per person will exist in the first table and history table will keep all the request  eg: </p>
  
  <pre><code>----------------
  |Name|Phone|Dob| (all columns are varchar2(255) type)
  ----------------
  
  --------------------------
  |Name|Phone|Dob|InsertTime (inserttime is date format defaults to now())
  --------------------------
  </code></pre>
  
  <p>I created a java object say, Person with private variables, getters and setters for Name, Phone and Dob.</p>
  
  <pre><code>public class Person {
  
      private String name;
      private String phone;
      private String dob;
  
      public String getName() {
          return name;
      }
      public void setName(String name) {
          this.name = name;
      }
      public String getPhone() {
          return phone;
      }
      public void setPhone(String phone) {
          this.phone = phone;
      }
      public String getDob() {
          return dob;
      }
      public void setDob(String dob) {
          this.dob = dob;
      }
      public String getInsertSQL(){
          return "INSERT INTO TABLE (NAME,PHONE,DOB) VALUES (?,?,?)";
      }
  
  }
  </code></pre>
  
  <p>java code for DB insertion side, i have something like this (DB is Oracle 10g)</p>
  
  <pre><code>public int insertProfile(Person p){
      int ret = 0;
      String sql = p.getInsertSQL();
      Connection conn = getConnection();
      PreparedStatement ps = null;
      try {
          ps = conn.prepareStatement(sql);
          ps.setString(1, p.getName());
          ps.setString(2, p.getPhone());
          ps.setString(3, p.getDob());
  
          ret = ps.executeUpdate();
  
      } catch (Exception e) {
          log error;
      } finally {
          try {
              closeConnection(ps, conn);
          } catch (SQLException e) {
              log error;
          }
      }
      return ret;
  }
  </code></pre>
  
  <p>So i use the same java object to insert into both the tables as they are very simialr except for the insertTime column which is default value of NOW().As expected, everything works fine here.</p>
  
  <p><em>note: My exact tables and java objects are quite huge to put it here so I tried to simplify but the scenario is still the same.</em> </p>
  
  <p>Now, for some reason I decided to change the dob column to a DATE type only for the first table <strong>and not the second</strong>. So I expect my code to break here because on java side the input is still a String format for DOB and I have not really done any date conversion in the my query. Having said, i am using the same Java object for both the table inserts, so one of the tables should definitely fail due to the same reason.</p>
  
  <p>But my code works fine. Both tables are updated correctly. first table is in date type and the second one is in varchar2(255) type.</p>
  
  <p>I tried to break the code by purposely adding a character in DOB input, good that it fails. But when i input a proper date in format DDMMYYYY, it somehow accepts and works fine on both the tables.
  I tried to input something like '99990101', i get error message saying 'invalid Month' for the first table insert however, the second table is updated properly. So there is definitely some date conversion going on. If i had put some date conversion code somewhere, then i would expect my insert on the second table should fail. But its not the case. I even tried to manual insert statement in my db editor, </p>
  
  <pre><code>insert into table (NAME,PHONE ,DOB) values ('Vimal','90837745','01019999');
  </code></pre>
  
  <p>it fails with "literal does not match format string" for table and no problem for the second. Just to clear the doubt if i had really converted the column to DATE format.</p>
  
  <p>My intention is actually to change the object Person.getInsertStmt to </p>
  
  <pre><code>public String getInsertSQL(){
              return "INSERT INTO TABLE (NAME,PHONE,DOB) VALUES (?,?,to_date(?,'DDMMYYYY'))";
          }
  </code></pre>
  
  <p>But i am not able to explain why the code still works without any change! 
  Can someone help to explain what exactly is happening? Is it to do with some database default date format or something?</p>
  
  <p>Thanks in advance guys.</p>