FoxPro Date format

It appears that the problem is that you're trying to pass the dates (e.g.: '1990/01/03') as character string information into VFP6 which is expecting date field formatted data.  That should correctly throw a "Data type mismatch" error.

Also, the default date format for the VFP ODBC driver seems to be the American format, mm/dd/yy.
PRB: ODBC Query Fails with Non-American Date Format
http://support.microsoft.com/default.aspx?scid=kb;en-us;229854

I'm not familiar enough with Cold Fusion to know if it has any odd requirements in making this to work, but, besides putting the date in American format, I would try putting braces around the date data, {01/03/1990}, in place of the single quote delimiters.  The braces within VFP tell it to interpret the enclosed data as date formatted data.

Trying to get that data to SQL Server, another format may prove better where the data meets this format {d 'yyyy-mm-dd'}.  But, I don't think that is required here.

Hope this is the information you need to get it working.

BTW, I didn't test what I offered and the ^ (caret) may very well be fine, but I do know for sure that you need to pay attention to the format that the ODBC driver requires by default, the American format.

It will be easy for you test on your end and you should be up and running in no time at all.

Curly braces, caret and strict YYYY/MM/DD format is well recognized by VFP ODBC driver and should the one used.

If you just want to get a table over into Oracle where your Oracle dba can take it from there, you can determine a target
location with the Oracle dba and the table name to be seen and simply use the Oracle Upsizing Wizard.  Visual FoxPro 6 is the last version version of Visual FoxPro that actually has the Oracle Upsizing Wizard included.  Now only the SQL Server Upsizing Wizard is available, but the steps to execute the Wizard whatever the back-end are the same.

To run the Oracle Upsizing Wizard

From the Wizards submenu on the Tools menu, choose Upsizing.

In the Wizard Selection dialog box, choose Oracle Upsizing Wizard.

Wizard Steps
Step 1 - Select Local Database
Step 2 - Select Data Source
Step 3 – Choose Tables
Step 4 - Map Field DataTypes
Step 5 – Select Tablespaces
Step 6 - Select Tablespace Files
Step 7 – Specify Clusters
Step 8 - Specify Cluster Tables
Step 9 - Specify Cluster Key
Step 10 - Set Upsizing Options
Step 11 - Finish

Further references on dates:

StrictDateFormat
http://fox.wikis.com/wc.dll?Wiki~StrictDateFormat~VFP

From VFP6 Command Window (or a Search in Help):
HELP StrictDateEntry Property

While there are lots of mechanisms to make the date and date data do different things, you need to know why they do what they do and when you should and should not use them.

PRB: ODBC Query Fails with Non-American Date Format
http://support.microsoft.com/default.aspx?scid=kb;en-us;229854

try this command
i m sure ur problem will b solved

SET STRICTDATE TO [0 | 1 | 2]

Lovleet

I tried {^1990/01/03} format date but it's return the same error:

ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Visual FoxPro Driver]Data type mismatch.


SQL = "INSERT INTO tmpmodrc (Asset_code,modif_no,policy,issue_date,kit_part,sending,price,fact_start,fact_done,codechange,mod_type,mod_id,descript,manual,last_date,last_time) VALUES ('CPLE-A',' 1','M',{^1990/01/03},'E077572 & more','40',2,'774',' ',' ','O',' ','Reduce the noise effect on the FBS-B/C measurement. Serial N. 764 has been done.','CPLT-A',{^1990/01/03},122843)"

I tried also {^19900103} and always the same error...


 

I tried to guide you into the correct answer, but you already accepted a wrong answer as the Accepted Answer.

Good luck.

Carl,
Would you teach me what's wrong there. Thanks. (with a sample please - you could use say orders, employee etc from testdata.dbc)

Just read my responses.

Thanks all,

It seems working now !!
I opened dbf file in local and changed options (in Regional option, you have date/time configuration) and I sent file to ODBC server.
And now, the system accept syntax {^1990/01/03} and I hope this will continue...

Many thanks Carl and cbasoz

Glad it's working, but you shouldn't have to change Regional normally.

On to bigger and better problems.

Carl,
I did read your reply and well the link you supplied. Wish you also read my reply to carefully :

"Curly braces, caret and strict YYYY/MM/DD format is well recognized by VFP ODBC driver and should the one used."

Do not always trust documentation w/o trial before claiming something is wrong.
You have the link, you have the sample code. Just try :

lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {^1993/07/22}"

instead of :

lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {^93/07/22}"


Please show me a solid sample that this format would fail.

Regards

Your answer as it stood failed for the poster.

You don't mean a developer should be expected to go to each user's PC and change the Regional setting for basics to work?  I have never had to change my Regional settings to make ODBC work.  And there is no one answer for all situations.  There are exceptions as witnessed by the info I posted.

The poster has it working now, but I wouldn't want to be the one who gets to set user's PCs Regional settings to make it work, especially if they're remote machines.  But even 50 locally will be cumbersome enough, not to mention changing that could affect other programs or applications that used to depend on that setting the way it was.

Or the poster was looking at a wrong place thinking it failed ?
I say again show me a solid sample.
It has nothing to do with regional settings. I thought you already knew {^YYYY/MM/DD} is free from regional settings.
Whatever the regional settings are in VFP and in control panel it WORKS ! Period.

There you go with absolutism again.