Micheal,
Your query trouble shooting tip showed me my error - see below:
Sorry here is the SHOW CREATE TABLE:
mysql> show create table `maintenance`;
+-------------+-------------------------------------------------------------
------------------------------------------------------------------------+
| Table | Create Table
|
+-------------+-------------------------------------------------------------
------------------------------------------------------------------------+
| maintenance | CREATE TABLE `maintenance` (
`indate` date DEFAULT NULL,
`contract` char(25) NOT NULL,
`cstatus` char(20) DEFAULT NULL,
`customer` char(35) DEFAULT 'CISCO SYSTEMS, INC',
`party` int(11) DEFAULT NULL,
`city` varchar(125) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) DEFAULT NULL,
`serial` char(25) DEFAULT NULL,
`model` char(25) DEFAULT NULL,
`mdesc` char(50) DEFAULT NULL,
`service` char(50) DEFAULT NULL,
`qty` int(11) DEFAULT NULL,
`amc` int(11) DEFAULT NULL,
`sdate` date DEFAULT NULL,
`edate` date DEFAULT NULL,
`cdate` date DEFAULT NULL,
`days` int(11) DEFAULT NULL,
`due` int(11) DEFAULT NULL,
`comments` char(50) DEFAULT NULL,
`lstatus` char(20) DEFAULT 'Installed',
`b2customer` char(50) DEFAULT NULL,
`descr` char(50) DEFAULT NULL,
`amlp` int(11) DEFAULT NULL,
`rcsdate` date DEFAULT NULL,
`rcedate` date DEFAULT NULL,
`contractid` int(11) DEFAULT NULL,
`refresh` char(8) DEFAULT NULL,
KEY `contract` (`contract`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------------------------------
------------------------------------------------------------------------+
1 row in set (0.00 sec)
Here are the two select statements per the two records below:
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(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS
RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\',
\'CX300-FD\', \'CX300 W 2GB FIELD
INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'38744\', \'39839\',
\'39840\', \'40451\', 612, 742892)
Doesn't look quite right - does it? ;-).
Well, great trouble shooting lesson to resolve a pretty stupid error - I was
using the $sdate variable for my insert which was pre-Julian reformat. I
needed $sdate1 for the insert. Here is the corrected query:
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(),\'CISCO SYSTEMS INC\', \'61420644H\', \'ACTIVE', \'CISCO SYSTEMS
RICHARDSON (TACSUN)\', 8200137, \'RICHARDSON\', \'TX\', \'APM00060300673\',
\'CX300-FD\', \'CX300 W 2GB FIELD
INSTALL\', \'PREMIUM HARDWARE SUPPORT\', 1, 1400.04, \'2006-1-28\',
\'2009-1-27\', \'2009-1-28\', \'2010-10-1\', 612, 742892)
I appear to be getting the proper dates in my table now.
Thanks for your help Michael!
Hagen
On 7/14/09 2:08 PM, "Michael Dykman" <mdyk...@gmail.com> wrote:
> 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<finha...@comcast.net> 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" <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
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
