General information
Title:
Problem with passing dates
I'm new to FoxPro and I'm trying to build a SQL query to pass data from a FoxPro database to an Oracle database. The date fields (DOA, DOB, UPD) are giving me problems. I tried first passing them directly as just
+ m.DOA + ",";
But that gave me an operator/operand type mismatch. I worked around that by converting it to char with DTOC and then TO_DATEing the result on the query, but this generates errors when the value is null. (I'd guess from trying to TO_DATE(' / / ') I'm thinking there must be a way to get the date passed directly in date format from foxpro into the sql statement, but I don't know how. Any ideas?
bschnick@tfn.net
The code is pasted below:
sqlString = "insert into SFHL_DATA.ARRESTS " + ;
"(ARRESTS_NUM, TICK_NUM, TICK_CNTY, CASE_NUM, MGTCODE, PATROL, ZONE, RPU, NETBAN, DOA, " + ;
"TOA, EMPID, EVID_NUM, FISHERY, FISHQTY, L_NAME, F_NAME, MI, ADDRESS, CITY, " + ;
"ST, ZIP, RESID, DOB, SEX, RACE, LAT, LONGITUDE, COM_FISHER, REG_NUM, DLNO, " + ;
"BUSI, ATYPE, CITIZEN, WEAPON, DRUG, ALCOHOL, JDISP, SR1, SR2, SR3, SR4, " + ;
"AT1, AT2, AT3, AT4, S1, S2, S3, S4, DACT1, DACT2, DACT3, DACT4, DTYPE1, DTYPE2, DTYPE3, " + ;
"DTYPE4, DQTY1, DQTY2, DQTY3, DQTY4, DUNIT1, DUNIT2, DUNIT3, DUNIT4, ACC, UPD, WHO, ORIG_TABLE) " + ;
"values (1, " ;
+ "'" + alltrim(m.TICK_NUM) + "', " ;
+ "'" + alltrim(m.TICK_CNTY) + "', ";
+ "'" + alltrim(m.CASE_NUM) + "', ";
+ "'" + alltrim(m.MGTCODE) + "', ";
+ "'" + alltrim(m.PATROL) + "', ";
+ "'" + alltrim(m.ZONE) + "', ";
+ "'" + alltrim(m.RPU) + "', ";
+ "'" + alltrim(m.NETBAN) + "', ";
+ "TO_DATE('" + DTOC(m.DOA) + "','MM/DD/YYYY'), ";
+ "TO_DATE('" + sTimeArrest + "','HH24:MM'), ";
+ "'" + alltrim(m.EMPID) + "', ";
+ "'" + alltrim(str(m.EVID_NUM)) + "', ";
+ "'" + alltrim(FISHERY) + "', ";
+ "'" + alltrim(str(m.FISHQTY)) + "', ";
+ "'" + alltrim(m.L_NAME) + "', ";
+ "'" + alltrim(m.F_NAME) + "', ";
+ "'" + alltrim(m.MI) + "', ";
+ "'" + alltrim(m.ADDRESS) + "', ";
+ "'" + alltrim(m.CITY) + "', ";
+ "'" + alltrim(m.ST) + "', ";
+ "'" + alltrim(m.ZIP) + "', ";
+ "'" + alltrim(m.RESID) + "', ";
+ "TO_DATE('" + DTOC(m.DOB) + "','MM/DD/YYYY'), ";
+ "'" + alltrim(m.SEX) + "', ";
+ "'" + alltrim(m.RACE) + "', ";
+ "'" + alltrim(m.LAT) + "', ";
+ "'" + alltrim(m.LONGITUDE) + "', ";
+ "'" + alltrim(m.COM_FISHER) + "', ";
+ "'" + alltrim(m.REG_NUM) + "', ";
+ "'" + alltrim(m.DLNO) + "', ";
+ "'" + alltrim(m.BUSI) + "', ";
+ "'" + alltrim(str(m.ATYPE)) + "', ";
+ "'" + alltrim(m.CITIZEN) + "', ";
+ "'" + alltrim(m.WEAPON) + "', ";
+ "'" + alltrim(str(m.DRUG)) + "', ";
+ "'" + alltrim(str(m.ALCOHOL)) + "', ";
+ "'" + alltrim(str(m.JDISP)) + "', ";
+ "'" + alltrim(m.SR1) + "', ";
+ "'" + alltrim(m.SR2) + "', ";
+ "'" + alltrim(m.SR3) + "', ";
+ "'" + alltrim(m.SR4) + "', ";
+ "'" + alltrim(str(m.AT1)) + "', ";
+ "'" + alltrim(str(m.AT2)) + "', ";
+ "'" + alltrim(str(m.AT3)) + "', ";
+ "'" + alltrim(str(m.AT4)) + "', ";
+ "'" + alltrim(m.S1) + "', ";
+ "'" + alltrim(m.S2) + "', ";
+ "'" + alltrim(m.S3) + "', ";
+ "'" + alltrim(m.S4) + "', ";
+ "'" + alltrim(m.DACT1) + "', ";
+ "'" + alltrim(m.DACT2) + "', ";
+ "'" + alltrim(m.DACT3) + "', ";
+ "'" + alltrim(m.DACT4) + "', ";
+ "'" + alltrim(m.DTYPE1) + "', ";
+ "'" + alltrim(m.DTYPE2) + "', ";
+ "'" + alltrim(m.DTYPE3) + "', ";
+ "'" + alltrim(m.DTYPE4) + "', ";
+ "'" + alltrim(str(m.DQTY1)) + "', ";
+ "'" + alltrim(str(m.DQTY2)) + "', ";
+ "'" + alltrim(str(m.DQTY3)) + "', ";
+ "'" + alltrim(str(m.DQTY4)) + "', ";
+ "'" + alltrim(m.DUNIT1) + "', ";
+ "'" + alltrim(m.DUNIT2) + "', ";
+ "'" + alltrim(m.DUNIT3) + "', ";
+ "'" + alltrim(m.DUNIT4) + "', ";
+ "'" + alltrim(m.ACC) + "', ";
+ "TO_DATE('" + DTOC(m.UPD) + "','MM/DD/YYYY'), ";
+ "'" + alltrim(m.WHO) + "', ";
+ "'ARREST')"
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only