I am trying to use DataTable.Select (string) to filter out rows on two columns: the first column is type string, the second: DateTime. This is how the string looks:
string sql = "symbol = #" + ( string )arrL1[ii] + "# AND dateTime = #" + Convert.ToString (dated) + "#";
symbol is a column name of System.String Type and dateTime is a column name with DateTime Type; dated is a variable type DateTime with a specific date and time. I verified its value. It is a valid, non null value. I get this error:
The string was not recognized as a valid DateTime Type There is an unknown word starting at index 0.
In other words the first character is unacceptable.
I tried to remove '#', then I replaced '#' with a single quote--the same error.
I need help.
Thank you very much.
DataTable.Select (string) problem
Dinesh Kulkarni - MSFT
One of the rule when working with DataTable is to always set the locale for that table. Use for example CurrentCulture if you don't want to think on (in this case datetime) format of string, or if you want then, set the locale to some defined, and use that locale datetime format. First is easy, not just for datetime, but also for floating point types.
Your source will be like this:
table.Locale = CultureInfo.CurrentCulture;
then when you need to select based on condition, your select commant will be:
table.Select("symbol = '" + ( string ) arrL1[ii] + "' AND dateTime = '" + dated + "'")
you need to enclose the date in single quotes also. .NET will take care of datetime format, because if will convert dated variable to string (i didn't put there .ToString()) but it will be the same, and because your datatable uses CurrentCulture Locale, it will be the same format, so it will not gives you troubles.
Personally i try to always use typed coding, so this is out of that rule. That's why i don't use this in my source. I always use typed collections and execute my select commands manually with methods, if not directly in the database using for that purspose dedicated stored procedure.
John Basedow
Just want to remind you, guys, that this post has received no attention so far and the problem is burning.
Thanks for any input.
JamesE
Ok, you are looking in the wrong place, date is ok, but you are trying to convert symbol value to DataTime by using #
string sql = "symbol = '" + ( string )arrL1[ii] + "' AND dateTime = #" + Convert.ToString (dated) + "#";
Mory0Tiki
Many thanks to Jocular Joe, Jeff Wharton and boban.s. I will try the code tomorrow. It is dynamic situation for me. I do the conversion while there is a download and it occurs only during certain hours of weekdays.
I am not sure if I understood the last post by boban.s correctly but want to make a clarification as to the format and setting I am using. It is .NET C# code. The table is DataTable. The data including the DateTime field arrive as series of bytes. They are converted to a string which is parsed and the end result for the DataTime column is date in the form MMddyyyy hhmmss AM/PM. In SQL Server I see it as "11/21/2006 10:58:25 AM" as an example.
The records from the DataTable are sent to SQL server and written to a database once a certain number (about 3000) have accumulated. There is some redundancy in the input stream coming from the server. I want to select() some records in DataTable according to the criterion I showed in my first post and compact them. In other words I want to pull some field values from a set of about 9 records, write them into the first record and delete the rest of them. This way I will have reduced the data trafic between the app and SQL server. I hope it will improve performance considerably.
At the link for MSDN help: ms-help://MS.VSCC.v80/MS.MSDN.vAug06.en/cpref4/html/M_System_Data_DataTable_Select_1_16219e3a.htm there is an example of how to form the string as an argument for the DataTable.Select(string) method. It is convenient for the fact that it shows a date example. It is inconvenient in the sense that the date is a string date and not a DataTime object. This is how they use the delimiter:
string expression;
expression = "Date > #1/1/00#";
DataRow[] foundRows;
..............................
I guess that's it. Thanks for all your help again. The boban.s' suggestion to set up the Culture parameter "globally" is great and I will do it first.
Exo_23
I am sorry, I do not quite get it. The date is OK, I know it. I haven't come here for that. I am not trying to convert symbol value to DateTime by using # either. It is a way off, I am sorry.
I guess I should reformulate my question. What is the format for including DateTime values in the DataTable.Select (string) method as an argument Let say I have just one argument and it is DateTime. How shall I convert it to string for this method to accept it I am not asking how to convert DateTime to string in general!!! I am asking for the format for this specific method. I tried to include (concatenate) the DateTime value without any delimiter--it gave me the same error. It was not a compilation error--it was a runtime arror.
Thanks.
Nishant Sivakumar
Date values must be formated as mm/dd/yyyy which is ok for people who's normal date format is like this (i.e. America), however here in Australia our format is dd/mm/yyyy which means we must manually convert the date to mm/dd/yyyy.
Unfortunately setting table.Locale = CultureInfo.CurrentCulture; does not result in dates being formatted correctly.
portect
However, none of date formats can't be mm/dd/yyyy or dd/mm/yyyy because this is not a date format.
Subasree
The documentation's a bit sparse (it's in the description of DataColumn.Expression) but ...
You need the # characters around the date/time. The string between the # characters will be parsed using CultureInfo.InvariantCulture - therefore you need to write the string using InvariantCulture if you're running in a non-US culture, e.g.:
string sql = ... "AND dateTime = #" + dated.ToString(CultureInfo.InvariantCulture) + "#"
Note that this expression will look for an exact match of the date and time. If you're only interested in the date and want to ignore the time, you can use a range in the expression, e.g.:
string sql = ... "AND dateTime >= #" + dated.ToString(CultureInfo.InvariantCulture,"MM/dd/yyyy") +
"# AND dateTime < #" + dated.AddDays(1.0D).ToString(CultureInfo.InvariantCulture,"MM/dd/yyyy") + "#"
Also as noted by mmix above, the string literal should not be surrounded by #, i.e. instead of:
"symbol = #" + ( string )arrL1[ii] + "# AND dateTime ...
You should be using single quotes as delimiters for the string:
"symbol = '" + ( string )arrL1[ii] + "' AND dateTime ...