DATE field value lost after INSERT INTO

[ChrisRChamberlain]

Hi all

Migrating a VFP database to MySql, see thread436-1618970, and have found that all the date values in the receiving MySql table have a value of

0000-00-00

This is how a MySql table might be created

CREATE TABLE INVOICE (ACCOUNT DECIMAL(7, 2) NULL, BALANCE DECIMAL(13, 2) NULL, CASH DECIMAL(7, 2) NULL, CAR_SALES BIT(1) NULL, CHEQUE DECIMAL(7, 2) NULL, CRE_TOTAL DECIMAL(7, 2) NULL, CREDITCARD DECIMAL(7, 2) NULL, CREDITNOTE BIT(1) NULL, COMPANY VARCHAR(31) NULL, DATE_RECD DATE NULL, DATE_REQ DATE NULL, ENGINETYPE VARCHAR(7) NULL, ENV_DESC_1 VARCHAR(80) NULL, FIRSTNAME VARCHAR(15) NULL, FUEL DECIMAL(3, 0) NULL, KEEP_RECNO DECIMAL(6, 0) NULL, JOB_DESC VARCHAR(30) NULL, JOB_NO DECIMAL(6, 0) NULL, INDIVIDUAL BIT(1) NULL, INV_DATE DATE NULL, INS_EXCESS BIT(1) NULL, INVOICE_NO DECIMAL(6, 0) NULL, INTERNAL DECIMAL(7, 2) NULL, MIL_DRIVEN DECIMAL(6, 0) NULL, MILEAGE DECIMAL(6, 0) NULL, MOT_DESC_1 VARCHAR(50) NULL, MOT_TEST BIT(1) NULL, NEXT_MOT DATE NULL, NEXT_SERV DATE NULL, RECORD_NO DECIMAL(5, 0) NULL, REN_DESC_1 VARCHAR(80) NULL, SER_DESC_1 VARCHAR(80) NULL, ROA_DESC_1 VARCHAR(80) NULL, SER_INTERV DECIMAL(2, 0) NULL, ROA_TOTAL DECIMAL(7, 2) NULL, SER_TOTAL DECIMAL(7, 2) NULL, TIME_RECD VARCHAR(8) NULL, TIME_REQ VARCHAR(8) NULL, TUN_DESC_1 VARCHAR(80) NULL, TUN_TOTAL DECIMAL(7, 2) NULL, WARRANTY BIT(1) NULL, REN_TOTAL DECIMAL(7, 2) NULL, ENV_TOTAL DECIMAL(7, 2) NULL, LAB_DESC_1 VARCHAR(80) NULL, LAB_DESC_2 VARCHAR(80) NULL, LAB_DESC_3 VARCHAR(80) NULL, LAB_DESC_4 VARCHAR(80) NULL, LAB_DESC_5 VARCHAR(80) NULL, LAB_DESC_6 VARCHAR(80) NULL, LAB_DESC_7 VARCHAR(80) NULL, LAB_DESC_8 VARCHAR(80) NULL, LAB_DESC_9 VARCHAR(80) NULL, LAB_DESC10 VARCHAR(80) NULL, LAB_DESC11 VARCHAR(80) NULL, LAB_DESC12 VARCHAR(80) NULL, LAB_DESC13 VARCHAR(80) NULL, LAB_DESC14 VARCHAR(80) NULL, LAB_DESC15 VARCHAR(80) NULL, LAB_DESC16 VARCHAR(80) NULL, SUB_DESC_1 VARCHAR(80) NULL, SUB_DESC_2 VARCHAR(80) NULL, SUB_DESC_3 VARCHAR(80) NULL, REC_DESC_1 VARCHAR(100) NULL, REC_DESC_2 VARCHAR(100) NULL, REC_DESC_3 VARCHAR(100) NULL, LAB_COST_1 DECIMAL(7, 2) NULL, LAB_COST_2 DECIMAL(7, 2) NULL, INV_TOTAL DECIMAL(13, 2) NULL, LAB_COST_3 DECIMAL(7, 2) NULL, LAB_COST_4 DECIMAL(7, 2) NULL, LAB_COST_5 DECIMAL(7, 2) NULL, LAB_COST_6 DECIMAL(7, 2) NULL, LAB_COST_7 DECIMAL(7, 2) NULL, LAB_COST_8 DECIMAL(7, 2) NULL, LAB_COST_9 DECIMAL(7, 2) NULL, LAB_COST10 DECIMAL(7, 2) NULL, LAB_COST11 DECIMAL(7, 2) NULL, LAB_COST12 DECIMAL(7, 2) NULL, LAB_COST13 DECIMAL(7, 2) NULL, LAB_COST14 DECIMAL(7, 2) NULL, LAB_COST15 DECIMAL(7, 2) NULL, LAB_COST16 DECIMAL(7, 2) NULL, TYRE_FNEAR DECIMAL(4, 2) NULL, TYRE_ROFFS DECIMAL(4, 2) NULL, TYRE_RNEAR DECIMAL(4, 2) NULL, TYRE_FOFFS DECIMAL(4, 2) NULL, TYRE_SPARE DECIMAL(4, 2) NULL, SUB_COST_1 DECIMAL(7, 2) NULL, SUB_COST_2 DECIMAL(7, 2) NULL, SUB_COST_3 DECIMAL(7, 2) NULL, CON_TOTAL DECIMAL(7, 2) NULL, TECHNICIAN VARCHAR(25) NULL, COSTED_BY VARCHAR(4) NULL, MOT_NO VARCHAR(20) NULL, LAB_TOTAL DECIMAL(7, 2) NULL, RECOMMEN_1 VARCHAR(120) NULL, RECOMMEN_2 VARCHAR(120) NULL, RECOMMEN_3 VARCHAR(120) NULL, PAR_DESC_1 VARCHAR(60) NULL, PAR_DESC_2 VARCHAR(60) NULL, PAR_DESC_3 VARCHAR(60) NULL, PAR_DESC_4 VARCHAR(60) NULL, PAR_DESC_5 VARCHAR(60) NULL, PAR_DESC_6 VARCHAR(60) NULL, PAR_DESC_7 VARCHAR(60) NULL, PAR_DESC_8 VARCHAR(60) NULL, PAR_DESC_9 VARCHAR(60) NULL, PAR_DESC10 VARCHAR(60) NULL, PAR_DESC11 VARCHAR(60) NULL, PAR_DESC12 VARCHAR(60) NULL, PAR_DESC13 VARCHAR(60) NULL, PAR_DESC14 VARCHAR(60) NULL, PAR_DESC15 VARCHAR(60) NULL, PAR_DESC16 VARCHAR(60) NULL, PAR_DESC17 VARCHAR(60) NULL, PAR_DESC18 VARCHAR(60) NULL, PAR_DESC19 VARCHAR(60) NULL, PAR_DESC20 VARCHAR(60) NULL, PAR_DESC21 VARCHAR(60) NULL, PAR_QUAN_1 DECIMAL(3, 0) NULL, PAR_QUAN_2 DECIMAL(3, 0) NULL, PAR_QUAN_3 DECIMAL(3, 0) NULL, PAR_QUAN_4 DECIMAL(3, 0) NULL, PAR_QUAN_5 DECIMAL(3, 0) NULL, PAR_QUAN_6 DECIMAL(3, 0) NULL, PAR_QUAN_7 DECIMAL(3, 0) NULL, PAR_QUAN_8 DECIMAL(3, 0) NULL, PAR_QUAN_9 DECIMAL(3, 0) NULL, PAR_QUAN10 DECIMAL(3, 0) NULL, PAR_QUAN11 DECIMAL(3, 0) NULL, PAR_QUAN12 DECIMAL(3, 0) NULL, PAR_QUAN13 DECIMAL(3, 0) NULL, PAR_QUAN14 DECIMAL(3, 0) NULL, PAR_QUAN15 DECIMAL(3, 0) NULL, PAR_QUAN16 DECIMAL(3, 0) NULL, PAR_QUAN17 DECIMAL(3, 0) NULL, PAR_QUAN18 DECIMAL(3, 0) NULL, PAR_QUAN19 DECIMAL(3, 0) NULL, PAR_QUAN20 DECIMAL(3, 0) NULL, PAR_QUAN21 DECIMAL(3, 0) NULL, PAR_COST_1 DECIMAL(7, 2) NULL, PAR_COST_2 DECIMAL(7, 2) NULL, PAR_COST_3 DECIMAL(7, 2) NULL, PAR_COST_4 DECIMAL(7, 2) NULL, PAR_COST_5 DECIMAL(7, 2) NULL, PAR_COST_6 DECIMAL(7, 2) NULL, PAR_COST_7 DECIMAL(7, 2) NULL, PAR_COST_8 DECIMAL(7, 2) NULL, PAR_COST_9 DECIMAL(7, 2) NULL, PAR_COST10 DECIMAL(7, 2) NULL, PAR_COST11 DECIMAL(7, 2) NULL, PAR_COST12 DECIMAL(7, 2) NULL, PAR_COST13 DECIMAL(7, 2) NULL, PAR_COST14 DECIMAL(7, 2) NULL, PAR_COST15 DECIMAL(7, 2) NULL, PAR_COST16 DECIMAL(7, 2) NULL, PAR_COST17 DECIMAL(7, 2) NULL, PAR_COST18 DECIMAL(7, 2) NULL, PAR_COST19 DECIMAL(7, 2) NULL, PAR_COST20 DECIMAL(7, 2) NULL, PAR_COST21 DECIMAL(7, 2) NULL, PAR_TOTA_1 DECIMAL(11, 2) NULL, PAR_TOTA_2 DECIMAL(11, 2) NULL, PAR_TOTA_3 DECIMAL(11, 2) NULL, PAR_TOTA_4 DECIMAL(11, 2) NULL, PAR_TOTA_5 DECIMAL(11, 2) NULL, PAR_TOTA_6 DECIMAL(11, 2) NULL, PAR_TOTA_7 DECIMAL(11, 2) NULL, PAR_TOTA_8 DECIMAL(11, 2) NULL, PAR_TOTA_9 DECIMAL(11, 2) NULL, PAR_TOTA10 DECIMAL(11, 2) NULL, PAR_TOTA11 DECIMAL(11, 2) NULL, PAR_TOTA12 DECIMAL(11, 2) NULL, PAR_TOTA13 DECIMAL(11, 2) NULL, PAR_TOTA14 DECIMAL(11, 2) NULL, PAR_TOTA15 DECIMAL(11, 2) NULL, PAR_TOTA16 DECIMAL(11, 2) NULL, PAR_TOTA17 DECIMAL(11, 2) NULL, PAR_TOTA18 DECIMAL(11, 2) NULL, PAR_TOTA19 DECIMAL(11, 2) NULL, PAR_TOTA20 DECIMAL(11, 2) NULL, PAR_TOTA21 DECIMAL(11, 2) NULL, PAR_TOTAL DECIMAL(13, 2) NULL, SUN_TOTAL DECIMAL(13, 2) NULL, MOT_COST DECIMAL(7, 2) NULL, SUB_TOTAL DECIMAL(13, 2) NULL, VAT DECIMAL(13, 2) NULL, MOT_TOTAL DECIMAL(7, 2) NULL, ADDRESS_1 VARCHAR(31) NULL, ADDRESS_2 VARCHAR(31) NULL, TOWN VARCHAR(31) NULL, COUNTY VARCHAR(21) NULL, POSTCODE VARCHAR(11) NULL, CONTACT VARCHAR(26) NULL, MOT_DATE DATE NULL, MAKE VARCHAR(15) NULL, MODEL VARCHAR(20) NULL, COLOUR VARCHAR(20) NULL, REG_NO VARCHAR(8) NULL, REG_DATE DATE NULL, ENGINE_NO VARCHAR(35) NULL, CHASSIS_NO VARCHAR(35) NULL, HOME_NO VARCHAR(15) NULL, WORK_NO VARCHAR(15) NULL, FAX_NO VARCHAR(15) NULL, MOBILE_NO VARCHAR(15) NULL, EXC_TOTAL DECIMAL(7, 2) NULL, SER_DEFI_1 BIT(1) NULL, SER_DEFI_2 BIT(1) NULL, SER_DEFI_3 BIT(1) NULL, SER_DEFI_4 BIT(1) NULL, SER_DEFI_5 BIT(1) NULL, SER_DEFI_6 BIT(1) NULL, SER_DEFI_7 BIT(1) NULL, SER_DEFI_8 BIT(1) NULL, SER_DEFI_9 BIT(1) NULL, SER_DEFI10 BIT(1) NULL, SER_DEFI11 BIT(1) NULL, TITLE VARCHAR(6) NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

and this how a typical row might be inserted

INSERT INTO INVOICE (account,balance,cash,car_sales,cheque,cre_total,creditcard,creditnote,company,date_recd,date_req,enginetype,env_desc_1,firstname,fuel,keep_recno,job_desc,job_no,individual,inv_date,ins_excess,invoice_no,internal,mil_driven,mileage,mot_desc_1,mot_test,next_mot,next_serv,record_no,ren_desc_1,ser_desc_1,roa_desc_1,ser_interv,roa_total,ser_total,time_recd,time_req,tun_desc_1,tun_total,warranty,ren_total,env_total,lab_desc_1,lab_desc_2,lab_desc_3,lab_desc_4,lab_desc_5,lab_desc_6,lab_desc_7,lab_desc_8,lab_desc_9,lab_desc10,lab_desc11,lab_desc12,lab_desc13,lab_desc14,lab_desc15,lab_desc16,sub_desc_1,sub_desc_2,sub_desc_3,rec_desc_1,rec_desc_2,rec_desc_3,lab_cost_1,lab_cost_2,inv_total,lab_cost_3,lab_cost_4,lab_cost_5,lab_cost_6,lab_cost_7,lab_cost_8,lab_cost_9,lab_cost10,lab_cost11,lab_cost12,lab_cost13,lab_cost14,lab_cost15,lab_cost16,tyre_fnear,tyre_roffs,tyre_rnear,tyre_foffs,tyre_spare,sub_cost_1,sub_cost_2,sub_cost_3,con_total,technician,costed_by,mot_no,lab_total,recommen_1,recommen_2,recommen_3,par_desc_1,par_desc_2,par_desc_3,par_desc_4,par_desc_5,par_desc_6,par_desc_7,par_desc_8,par_desc_9,par_desc10,par_desc11,par_desc12,par_desc13,par_desc14,par_desc15,par_desc16,par_desc17,par_desc18,par_desc19,par_desc20,par_desc21,par_quan_1,par_quan_2,par_quan_3,par_quan_4,par_quan_5,par_quan_6,par_quan_7,par_quan_8,par_quan_9,par_quan10,par_quan11,par_quan12,par_quan13,par_quan14,par_quan15,par_quan16,par_quan17,par_quan18,par_quan19,par_quan20,par_quan21,par_cost_1,par_cost_2,par_cost_3,par_cost_4,par_cost_5,par_cost_6,par_cost_7,par_cost_8,par_cost_9,par_cost10,par_cost11,par_cost12,par_cost13,par_cost14,par_cost15,par_cost16,par_cost17,par_cost18,par_cost19,par_cost20,par_cost21,par_tota_1,par_tota_2,par_tota_3,par_tota_4,par_tota_5,par_tota_6,par_tota_7,par_tota_8,par_tota_9,par_tota10,par_tota11,par_tota12,par_tota13,par_tota14,par_tota15,par_tota16,par_tota17,par_tota18,par_tota19,par_tota20,par_tota21,par_total,sun_total,mot_cost,sub_total,vat,mot_total,address_1,address_2,town,county,postcode,contact,mot_date,make,model,colour,reg_no,reg_date,engine_no,chassis_no,home_no,work_no,fax_no,mobile_no,exc_total,ser_defi_1,ser_defi_2,ser_defi_3,ser_defi_4,ser_defi_5,ser_defi_6,ser_defi_7,ser_defi_8,ser_defi_9,ser_defi10,ser_defi11,title) VALUES (0,0,0,b'0',0,0,52.99,b'0','Glover',2009-09-21,2009-09-21,'Petrol','','B J',0,0,'MOT Test',18397,b'1',2009-09-21,b'0',21642,0,3863,25724,'Yes',b'1',2010-09-20,2010-03-20,2921,'','','',6,0,0,'08:30:00','05.00','',0,b'0',0,0,'','','','','','','','','','','','','','','','','','','','','','',0,0,52.99,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','000000000',0,'','','','','','','','','','','','','','','','','','','','','','','','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,26.95,26.95,4.04,22,'25 Elmlea Road','Kings Stanley','Nr Stonehouse','Gloucestershire','GL10 3HP','',2009-09-21,'Ford','Focus','Blue','CA05 OUM',2005-07-11,'5B35563','WF05XXWPD55B35583','01453 827152','01453','','',0,b'0',b'0',b'0',b'0',b'0',b'0',b'0',b'0',b'0',b'0',b'0','Mr')

As can be seen, the Date values are corrrect in the INSERT INTO statement, but disappear in the MySql table.

Ideas, please?

TIA

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk

 

[tsuji]

Date field should be similarly quoted as per the other thread. Otherwise, I don't see the problem, but I only look at the specific spot of Date.

 

[ChrisRChamberlain]

tsuji

Same resolution as in thread436-1618970.

Again thanks

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk