Hagan,
Close but not quite what I asked for. The schema for your table is
what you get from SHOW CREATE TABLE `mytable`. It will show all the
indexes and, most imporatantly, make it trivial for someone trying to
assist you in your investigation to re-create your problem.
It is good to see the quotes in your perl script, but what I was
hoping for was the query itself after perl has rendered it, again, so
it can be run against a database without me having to write a
perl-wrapper to do so.
On Tue, Jul 14, 2009 at 4:02 PM, Hagen Finley<finhagen@stripped> wrote:
> 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" <mdykman@stripped> 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<finhagen@stripped> 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
>>>
>>>
>>>
>>
>>
>
--
- michael dykman
- mdykman@stripped
- All models are wrong. Some models are useful.
