Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DATE field value lost after INSERT INTO

Status
Not open for further replies.
ChrisRChamberlain

ChrisRChamberlain

Programmer
Mar 23, 2000
3,392
GB
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
Code: 
0000-00-00
This is how a MySql table might be created
Code: 
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
Code: 
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 [pc2]
PDFcommander.com
motrac.co.uk
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
function argument value, type or count is invalid
Replies
3
Views
500
bharons
bharons
BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
737
BobThornton
BobThornton
Darryl Johnson
  • Locked
  • Question
Adding labor hours to a date time field
Replies
5
Views
273
Andrzejek
Andrzejek
BJZeak
  • Locked
  • Question
need help with .sql insert statement
Replies
1
Views
166
spamjim
spamjim
martinkaeser
  • Locked
  • Question
MySQL Query for incrementing field from duplicates in database
Replies
4
Views
232
martinkaeser
martinkaeser

Part and Inventory Search

Sponsor

Back
Top