Date Handling - Parse Dates with Oracle
(Page 3 of 4 )
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'/>.
Next: Parse Dates with MySQL >>
More SQL Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.
|
|
