Managed C++/CLI

"SELECT someDate FROM myTable WHERE someField = 34"

06/07/2010 12:00:00 AM

"SELECT * FROM MyTable WHERE someDate = #06/07/2010 12:00:00 AM#"

"SELECT someDate  FROM myTable  WHERE (someDate >= #6/07/2010#) AND (someDate <= #7/07/2010#)"

Dirkus Maximus wrote:

I do a 'ToString()' and that returns 06/07/2010 12:00:00 AM

/// <summary>
/// Formats an immediate date value
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static string Date(DateTime date) {
	return date.ToString("#yyyy-MM-dd#");
}

string query="... WHERE myDateField = "+Date(myDateTime)+"...";

Luc Pattyn wrote:

ToString() shows the data formatted like the user (you) said he wanted it, see the Control Panel ("Regional Settings" or "Dates and Times")

Note that this date literal must always be expressed in MM/DD/YY order. To avoid the ambiguity of the meaning of stored queries, Microsoft Jet doesn't follow the international date format settings specified in the user's Control Panel.

"SELECT * FROM MyTable WHERE someDate = #7/6/10#"

Dirkus Maximus wrote:

Microsoft Jet doesn't follow the international date format settings specified in the user's Control Panel.

Dirkus Maximus wrote:

Note that this date literal must always be expressed in MM/DD/YY order.

// store date as DateTime object after splitting the string and converting to ints
DateTime^ dt = gcnew DateTime(yearField, monthField, dayField);

// to insert into the DB, i use
String^ query = "INSERT INTO myTable (..., someDateField, ...)  Values(..., dt->ToString("yyyy'/'MM'/'dd"), ..."

array <String^>^ strArr = fromDate->Split('/');
int dayField = Convert::ToInt32(strArr[0]);
int monthField = Convert::ToInt32(strArr[1]);
String^ yearField = Convert::ToString(strArr[2]);
// the year field only takes the last 2 digits
yearField = yearField->Substring(2,2);
String^ formattedDate = String::Concat(" #",Convert::ToString(monthField),"/", Convert::ToString(dayField),"/", yearField,"# ");
query = "SELECT * FROM myTable WHERE someDate = " + formattedDate +  ";

Dirkus Maximus wrote:

Great article Luc - very informative

Dirkus Maximus wrote:

It's all a bit convoluted

Dirkus Maximus wrote:

I don't know why (as described in my first post) this 'one digit' day didn't work while the '2-digit' one did

Dirkus Maximus wrote:

JET SQL supports different types. You can declare parameter type by using PARAMETERS clause. The OleDB provider in ADO.Net does not support named parameters.

Luc Pattyn wrote:

I don't understand why you would do inserts and selects differently

String^ text = ((DateTime)datarow[4]).ToString("dd MMM yyyy")

String^ query = "SELECT * FROM myTable WHERE myDate = #" + text +  "#)";

Luc Pattyn wrote:

I have strong doubts your 2-digit dates did work correctly all the time

Arjen Tjallema wrote:

Is it possible to declare a managed variable within a class, without making the class a ref class? If I have to make it a ref class it produces a load of errors, as it is connected with parts of existing code that cannot deal with that.
Any ideas would be more than welcome!

glitteringsound wrote:

e.g I have read data from an XML file i.e BSTR field1,BSTR field2,BSTR field3,BSTR field4,BSTR field5,long field6