Date Handling - Convert Your Dates
(Page 2 of 4 )
Suppose you have dates in user-supplied input that are in this format—6/18/2006—and you need to create date literals for anINSERT statement such as this:DATE '2006-06-18'. Here’s how you can accomplish this in Perl:
foreach ('6/18/2006', '12/13/2006'){
if (/(\d+)\/(\d+)\/(\d\d\d\d)/){ # Capture date parts into $1, $2, $3
my $m = substr("0$1", -2); # Left-pad with zeros if needed
my $d = substr("0$2", -2);
my $y = $3;
$sql = "INSERT INTO d VALUES (DATE '$y-$m-$d')";
print "$sql\n";
} else{
warn "Could not parse date: $!";
}
}
Note that we in-lined the user-supplied values directly into theINSERTstatement. In theory, this would have opened us up to an SQL injection attack [Hack #48]. However, the input is fully sanitized in that the regular expression guarantees that$y,$m, and$dcontain only digits (\dmatches any one character between 0 and 9).
The output from this code is ready for use in MySQL, Oracle, PostgreSQL, or another engine that uses the SQL standard:
INSERT INTO d VALUES (DATE '2006-06-18');
INSERT INTO d VALUES (DATE '2006-12-13');
For Microsoft SQL Server, you need only drop the word DATE.
Table 4-1 shows some common variations of date formats.
Table 4-1. Finding a common date format
Engine | DATE '2006-06-01' | '2006-6-1' | '1 JUN 2006' |
MySQL | OK | OK | Error |
SQL Server | Error | OK | OK |
Table 4-1. Finding a common date format (continued)
Oracle Engine | OK DATE '2006-06-01' | Error '2006-6-1' | OK '1 JUN 2006' |
PostgreSQL DB2 | OK Error | OK OK | OK Error |
Mimer | OK | Error | Error |
Standard | OK | Error | Error |
No single format works with every engine; you can’t do better than satisfy any two of the three most popular platforms (SQL Server, MySQL, and Oracle).
You also cannot publish even the simplest SQL data in a format that everyone can read. For a nasty solution you can publish dates, but you must capitalize the word date in an odd way—for example,DaTe '2006-06-01'. SQL Server and DB2 users must do a case-sensitive search and replace to remove the stringDaTe, but users of other engines can just slurp the file into their engines directly. The advantage of using an unusual form of capitalization is that the SQL engines don’t care, but the stringDaTeis unlikely to occur in any other part of the file, so it’s really easy to pick up with a conversion script (you also could pipe or redirect your SQL to a one-liner, such asperl-pe 's/DaTe//g').
If MySQL comes across a date format it doesn’t recognize (such as'1 JUN 2006'), it accepts it without raising an error and puts in the valueDATE '0000-00-00'. However, if you check your warnings, you’ll see that something went wrong:
mysql> insert into d values ('1 JUN 2006');
Query OK, 1 row affected, 1 warning (0.13 sec)
mysql> show warnings\G
*************** 1. row *************
Level: Warning
Code: 1265
Message: Data truncated for column 'd' at row 1
1 row in set (0.00 sec)
If you are reading data in from another system you may be able to pass date strings in their original format and do the parsing in SQL. The Oracle example shows the general technique, but you’ll need to use different SQL functions for your database. We’ll show you those after the Oracle example.
Next: Parse Dates with Oracle >>
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.
|
|
