Hi Tom,

How about trying it this way;

lcDate = DTOC(NVL(ldMyDate, {}))

HTH,
Arriyel

>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')"