For the longest time I have been plagued with being unable to properly insert or update a date field. I have been using Foxpro 2.6 on an NT box using IIS.

Having tried all manner of formats (dd/mm/yyyy, dd-mm-yyyy, etc) nothing would work. I generally got the following error:

Warning: SQL error: [Microsoft][ODBC Visual FoxPro Driver]Data type mismatch., SQL state 22005 in SQLExecDirect

And the date inserted would be: 1899-12-30

After searching through the microsoft site though I finally found my error.

A date must be inserted (or updated) in the following manner:

INSERT INTO table VALUES ({mm-dd-yyyy})

I know that others on this forum have had similar difficulties so I hope this helps.

I believe you can insert it in a different format using ^ such as:

INSERT INTO table VALUES ({yyyy-mm-dd})

BUT I have not tried it so don't count on it.

Hope this helps.
PM

    a year later

    Thank you! Thank you!

    Of course, curly brackets. What was I thinking? I'll be so happy when I never have to deal with a FoxPro database ever again.

      Write a Reply...