Date formts in Foxpro SQL

SET DATE TO MDY
SELECT ...

Use the command

SET DATE AMERICAN

The output date format is based on the FoxPro settings.

You have to issue
SET DATE TO AMERICAN

for your format.

You may also look at
SET CENTURY
SET MARK
SET STRICTDATE

could I get a full example .

I am basically useing the code below.   And it works fine. I tried to lop off the time using TToD and it errors out my querry also.   so where in that code would I put the above statems.

string MySQL; 
MySQL = "SELECT `prdeds`.`amount`, `prdeds`.`curamount`, `prdeds`.`curemplyr`, `prdeds`.`dedcode`, `hrpersnl`.`p_active`, ";
MySQL+= "`hrpersnl`.`p_fname`,`hrpersnl`.`p_lname`,`hrpersnl`.`p_mi`, ttod(`hrpersnl`.`p_orighire`), `hrpersnl`.`p_ssn`, ";
MySQL+= "`hrpersnl`.`p_termdate`,`prdeds`.`qtdamount`,`prdeds`.`qtdemplyr`, `prdeds`.`ytdamount`,`prdeds`.`ytdemplyr`";
MySQL += ", `prdeds`.`mtdamount`,`prdeds`.`mtdemplyr`, ";
MySQL+= "`hrpersnl`.`p_birth`,`hrpersnl`.`p_hcity`,`hrpersnl`.`p_hstreet1`, `hrpersnl`.`p_hstreet2`, `hrpersnl`.`p_hzip`,";
MySQL+= "`hrpersnl`.`p_hstate`,`hrpersnl`.`p_married`,`hrpersnl`.`p_payfreq` ";
MySQL += "FROM   `hrpersnl` `hrpersnl` left JOIN `prdeds` `prdeds` ON `hrpersnl`.`p_empno`=`prdeds`.`empno` ";

conn.ConnectionString = "Provider=VFPOLEDB.1; Data Source=" + AppSettingInfo.DataFile + ";";
conn.Open();
DataSet DTAbra = new DataSet();
OleDbCommand SQL = new OleDbCommand(MySQL, conn);
OleDbDataAdapter adpter = new OleDbDataAdapter(SQL);
adpter.Fill(DTAbra);
DataTable User401K = DTAbra.Tables[0];
if (User401K.Rows.Count > 0)
{
 my fuction specifics here
}

Select allOpen in new window

What data type is the p_orighire column?

If you need to return given date as a string from SQL command then you have to pass the SET DATE command before the SQL, e.g.

conn.ExecuteNonQuery("EXECSCRIPT([SET DATE TO AMERICAN])")

But American date is a default for VFP OLE DB, so it could be enough to use:

DTOC(ttod(hrpersnl.p_orighire)) AS p_orighire

(single quotes are not necessary in VFP)

You may also remove the TTOD and DTOC functions and format the whole datetime in C#... Why do you need to format it at VFP side?

its a datetime field.

when do do the DTOC(ttod(hrpersnl.p_orighire)) AS p_orighire
I get the following error :

OleEBExceotion was unhandled
Function argument value, type, or count is invalid.

I am doing a basic copy to a local temp which to do a basic cross tab

Of course, the code continues:

dbConnection1.Open();
adapter.Fill(table);

// etc.

Select allOpen in new window

took your advice and did it in C# ...