Date Handling

Oracle has a neat function calledTO_DATE, which allows you to specify the pattern used in your input string:

  INSERT INTO d VALUES (TO_DATE('1 Jun 2006', 'dd Mon yyyy'))

You can specify a wide range of formats that include “filler” characters other than a space.

Using this technique, you could write a simple Perl script, for example. If Perl has read a string such as'1 Jun 2006'into the variable$v, you could generate the SQL as:

  my $sql = "INSERT INTO d VALUES (TO_DATE('$v', 'dd Mon yyyy'))";

If your dates are coming from an untrusted source, you should still check the pattern to guard against SQL injection attacks:

  if ($v !~ /^\d+ \w\w\w \d\d\d\d$/){
   warn "Injection attack.";
  }

If you were using XSLT, you might want to use code such as this:

  <stylesheet xmlns="http://www.w3.org/1999/XSL/Transform">
    <template match="foo">
      INSERT INTO dd VALUES (
        TO_DATE('<value-of select='@bar'/>',
               ,'dd Mon yyyy'))
    </template>
  </stylesheet>

That sheet would take care of input such as<foo bar='1 Jun 2006'/>.