Error with Datatable.select and DateTime

Question

I have a Datatable with a column "Datum" that is typeof(DateTime).

I want to select all rows for the actual month:

DataRow[] foundRows = dt.Select("Datum >='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "' AND Datum <='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)) + "'");

I'm getting the error

Cannot perform '<=' operation on System.String and System.DateTime

If I split the select statement the first one is running well, the second get the same error:

Select 1 is working: DataRow[] foundRows1 = dsLinie.Tables[0].Select("Datum >='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) + "'");

Select 2 get the error: DataRow[] foundRows2 = dsLinie.Tables[0].Select("Datum <='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)) + "'");

Why do I get the error, both select statements query the same DateTime column "Datum"?

Answer 1

I don't know, why the first is working and the second is not, but did you try to put the dates between # AND to explicitly cast the datetime-values to string?

DataRow[] foundRows = dt.Select("Datum >=#" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString() + "# AND Datum <=#" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)).ToString() + "#");

Answer 2

So I figured it out. I tried around with the second select and inserted the day manual:

DataRow[] foundRows2 = dsLinie.Tables[0].Select("Datum <='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month,1)) + "'");

This works until the day is not bigger than 12. So it has to be something with the format of the date between c# and sql. Found this answer for my problem:

Using the standard datetime format "s" will also ensure internationalization compatibility (MM/dd versus dd/MM):

myDateTime.ToString("s");

=> 2013-12-31T00:00:00

Complete working statement:

DataRow[] foundRows = dt.Select("Datum >='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("s") + "' AND Datum <='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)).ToString("s") + "'");