Home arrow SQL arrow Page 2 - Date Handling

Date Handling

SQL can handle many date calculations. This four-part series will show you how to do date calculations against your database and get reports based on specific periods of time. This article, the first in the series, is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Author Info:
By: O'Reilly Media
Rating: 5 stars5 stars5 stars5 stars5 stars / 13
December 06, 2007
  1. · Date Handling
  2. · Convert Your Dates
  3. · Parse Dates with Oracle
  4. · Parse Dates with MySQL

print this article

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


DATE '2006-06-01'


'1 JUN 2006'





SQL Server




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 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

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials