mysql> desc maintenance;
+------------+--------------+------+-----+--------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+--------------------+-------+
| indate | date | YES | | NULL | |
| contract | char(25) | NO | MUL | NULL | |
| cstatus | char(20) | YES | | NULL | |
| customer | char(35) | YES | | NUCO INC | |
| party | int(11) | YES | | NULL | |
| city | varchar(125) | YES | | NULL | |
| state | varchar(50) | YES | | NULL | |
| country | varchar(50) | YES | | NULL | |
| serial | char(25) | YES | | NULL | |
| model | char(25) | YES | | NULL | |
| mdesc | char(50) | YES | | NULL | |
| service | char(50) | YES | | NULL | |
| qty | int(11) | YES | | NULL | |
| amc | int(11) | YES | | NULL | |
| sdate | date | YES | | NULL | |
| edate | date | YES | | NULL | |
| cdate | date | YES | | NULL | |
| days | int(11) | YES | | NULL | |
| due | int(11) | YES | | NULL | |
| comments | char(50) | YES | | NULL | |
| lstatus | char(20) | YES | | Installed | |
| b2customer | char(50) | YES | | NULL | |
| descr | char(50) | YES | | NULL | |
| amlp | int(11) | YES | | NULL | |
| rcsdate | date | YES | | NULL | |
| rcedate | date | YES | | NULL | |
| contractid | int(11) | YES | | NULL | |
| refresh | char(8) | YES | | NULL | |
+------------+--------------+------+-----+--------------------+-------+
28 rows in set (0.02 sec)
I cleaned up the insert statement to push the dates as strings:
$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)");
Here are two rows from the spreadsheet:
NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX
APM00060300673 CX300-FD CX300 W 2GB FIELD INSTALL PREMIUM HARDWARE SUPPORT 1
$1,400.04 70.00% 01/27/06 01/26/09 01/27/09 09/30/10 $1,408.48 612 742892
NUCO INC 61420644H ACTIVE NUCO RICHARDSON (TACSUN) 8200137 RICHARDSON TX 0
CX-2G10-146 146GB 10K 2GB FC PREMIUM HARDWARE SUPPORT 15 $1,926.00 70.00%
01/27/06 01/26/09 01/27/09 09/30/10 $1,937.61 612 742892
Here is the print statement from the perl script:
+++++++++++++++++++++++++++++++++++
Bill to Customer: NUCO INC
Contract: 61420644H
Status: ACTIVE
Install Customer: NUCO RICHARDSON (TACSUN)
Party ID: 8200137
City: RICHARDSON
State: TX
Serial Number: APM00060300673
Model: CX300-FD
Model Description: CX300 W 2GB FIELD INSTALL
Description: PREMIUM HARDWARE SUPPORT
Quantity: 1
Annual List Price: 1400.04
Discount: 0.7
Start Date: 2006-1-28
End Date: 2009-1-27
Coverage Start Date: 2009-1-28
Coverage End Date: 2010-10-1
Pro-Rated Maintenance: 1408.47859726027
Days: 612
Contract ID: 742892
+++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++
Bill to Customer: NUCO INC
Contract: 61420644H
Status: ACTIVE
Install Customer: NUCO RICHARDSON (TACSUN)
Party ID: 8200137
City: RICHARDSON
State: TX
Serial Number: 0
Model: CX-2G10-146
Model Description: 146GB 10K 2GB FC
Description: PREMIUM HARDWARE SUPPORT
Quantity: 15
Maintenance List Price: 1926
Discount: 0.7
Start Date: 2006-1-28
End Date: 2009-1-27
Coverage Start Date: 2009-1-28
Coverage End Date: 2010-10-1
Pro-Rated Maintenance: 1937.60876712329
Days: 612
Contract ID: 742892
+++++++++++++++++++++++++++++++++++
Here is a select * for these two records:
mysql> select * from maintenance where contract='61420644H';
+------------+-----------+---------+-----------------------------------+----
-----+------------+-------+---------+----------------+-------------+--------
-------------------+---------+------+------+------------+------------+------
-+------+------+----------+-----------+-------------------+-----------------
---------+------+------------+------------+------------+---------+
| indate | contract | cstatus | customer |
party | city | state | country | serial | model |
mdesc | service | qty | amc | sdate | edate
| cdate | days | due | comments | lstatus | b2customer | descr
| amlp | rcsdate | rcedate | contractid | refresh |
+------------+-----------+---------+-----------------------------------+----
-----+------------+-------+---------+----------------+-------------+--------
-------------------+---------+------+------+------------+------------+------
-+------+------+----------+-----------+-------------------+-----------------
---------+------+------------+------------+------------+---------+
| 2009-07-14 | 61420644H | ACTIVE | NUCO RICHARDSON (TACSUN) | 8200137 |
RICHARDSON | TX | NULL | APM00060300673 | CX300-FD | CX300 W 2GB
FIELD INSTALL | NULL | 1 | NULL | 0000-00-00 | 0000-00-00 | NULL |
612 | NULL | NULL | Installed | NUCO INC | PREMIUM HARDWARE SUPPORT |
1400 | 0000-00-00 | 0000-00-00 | 742892 | NULL |
| 2009-07-14 | 61420644H | ACTIVE | NUCO RICHARDSON (TACSUN) | 8200137 |
RICHARDSON | TX | NULL | 0 | CX-2G10-146 | 146GB 10K 2GB
FC | NULL | 15 | NULL | 0000-00-00 | 0000-00-00 | NULL | 612
| NULL | NULL | Installed | NUCO INC | PREMIUM HARDWARE SUPPORT | 1926 |
0000-00-00 | 0000-00-00 | 742892 | NULL |
+------------+-----------+---------+-----------------------------------+----
-----+------------+-------+---------+----------------+-------------+--------
-------------------+---------+------+------+------------+------------+------
-+------+------+----------+-----------+-------------------+-----------------
---------+------+------------+------------+------------+---------+
2 rows in set (0.00 sec)
So as you can see my date inserts are returning ³0000-00-00². That¹s better
than ³2040-07-14² but I still get those with some of the inserts. It also
appears the Julian date is adding a day to the date although that doesn¹t
materially impact my project. Thoughts?
Hagen
On 7/14/09 12:44 PM, "Michael Dykman" <mdyk...@gmail.com> wrote:
> Hagan,
>
> your premise looks reasonable enough (excepting that your dates must
> always be in quotes in your insert statement.. the system is expecting
> a string).. might you show us your schema and perhaps a print-out of
> your query after it has been rendered by perl?
>
>
> - michael dykman
>
> On Tue, Jul 14, 2009 at 2:23 PM, Hagen Finley<finha...@comcast.net> wrote:
>> 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
>>
>>
>>
>
>
