Date Handling

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.

blog comments powered by Disqus