List:General Discussion« Previous MessageNext Message »
From:Hagen Finley Date:July 14 2009 6:23pm
Subject:Date Translation Issues
View as plain text  
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




Thread
Date Translation IssuesHagen Finley14 Jul
Re: Date Translation IssuesHagen Finley14 Jul
  • Re: Date Translation IssuesMichael Dykman14 Jul
    • Re: Date Translation IssuesHagen Finley14 Jul