I am attempting to parse Excel spreadsheets and insert the data into mysql. I am actually having quite a bit of success, but one issue I have not been able to address is ingesting dates. The unformatted date in Excel appears to be in the Julian format* (e.g. 10/28/2008 is 39749). There are three problems with that:
1) mysql expects dates formatted as 2008-10-28. I wrote a simple script to convert a standard US date format to mysql format:

#!/usr/bin/perl

use strict;
use warnings;

my $sdate0='10/28/2008';

my @sdate = split /\//, $sdate0;
my $sdate1 = join '-', $sdate[2],$sdate[0],$sdate[1];
print "$sdate1\n";
[download]

2) when I parse the date in the spreadsheet it actually is ingested as 39749 (sigh) so my split join script is of no value.
3) I did some research and it doesn't appear that mysql can convert Julian dates to it's date standard (2x sigh). Therefore, it looks like I need a perl script that can convert a Julian date to a mysql date. I looked on cpan and while there are some modules which address Julian dates, I didn't see anything that would do what I am trying to do. Does anyone have a Julian to mysql date conversion script?

*I typically receive the source spreadsheet via email in Office 97 xls format. I run Windows in a VM on top of a SUSE linux host os. When I save the xls file to my Unix (ext3) filesystem the date seems to distill down to the Julian format. I am running mysql and perl on the linux host and that appears to result in this Julian format issue.

Hagen Finley
Boulder, CO