I am using mysql Server version: 5.1.31 MySQL Community Server (GPL) on a
MAC 10.5 host. I am having some strange date translation issues when I
insert dates into my tables.
What I do is run perl¹s Spreadsheet::ParseExcel module with Time::JulianDay
to reformat excel dates to insert properly into mysql.
use Spreadsheet::ParseExcel;
use Time::JulianDay;
To reformat the date I run a small code sequence:
$sdate0 = $sdate + julian_day(1900, 1, 0);
($year, $month, $day) = inverse_julian_day($sdate0);
$sdate1 = join '-', $year,$month,$day;
This seems to work fine because I print my output to a file and the date
format appears to be correct:
+++++++++++++++++++++++++++++++++++
NUCO INC
M61037841
ACTIVE
61037841
2815041
RICHARDSON
TX
US
APM00051703278
CX-ATA-DAE
DAE ATA EXPANSION FOR CX
PREMIUM HARDWARE SUPPORT
5
5050.2
2005-8-5
2008-8-6
201
+++++++++++++++++++++++++++++++++++
So my dates show up in the 2008-6-15 format. The problem is when I insert
those dates into my table I either get all 0s or I get a 2040 year (with the
correct month and day):
NUCO INC | 2815487 | RESEARCH TRIANGLE PARK | NC | TN187401264 | 1 |
DMX1000-P2 | DMX-2 U PERF SNGL BAY | 0000-00-00 | 2040-08-06 | 365 | 23275
|
I have had this weird year issue pop up time to time but it was easy enough
to work around it. However, for my current project I really need the dates
in the table to match the dates in the source spreadsheet.
I do see that the dates I am inputing are not in a pure YYYY-MM-DD format-
missing 0s. Its possible that that is the issue but since the date is
getting converted from the underlying Julian date I am not sure how to fix
that.
In case it matters, here is my insert string:
$dbh->do("insert into maintenance
(indate, b2customer, contract, cstatus, customer, party, city, state,
serial, model, mdesc, descr, qty, amlp, sdate, edate, rcsdate, rcedate,
days, contractid)
values
(CURDATE(),\'$b2customer\', \'$contract\', \'$status\', \'$incustomer\',
$party, \'$city\', \'$state\', \'$serial\', \'$model\', \'$mdesc\',
\'$descr\', $qty, $amlp, $sdate, $edate, $rcsdate, $rcedate, $days,
$contractid)");
I have also tried inserting the data as a string but that doesn¹t seem to
make any difference.
Any help would be greatly appreciated. Thank you!
Hagen Finley
Boulder, CO
