ObjectQuery, passing datetime in Where clause filter

Question

How to pass in Date Time value here?

ObjectQuery<Item> _Query = ItemEntities.CreateQuery<Item>("Item");
_Query = _Query.Where("(it.StartDate >= 11/4/2009 5:06:08 PM)");

my sample code above does seem to work.

even with this

ObjectQuery<Item> _Query = ItemEntities.CreateQuery<Item>("Item");
_Query = _Query.Where("(it.StartDate >= \"11/4/2009 5:06:08 PM\")");

I got type cast error in EDM.

Answer 1

The following should work:

ObjectQuery<Item> _Query = ItemEntities.CreateQuery<Item>("Item");
_Query = _Query.Where("(it.StartDate >= DATETIME'11/4/2009 17:06:08')");

See the documentation for more information on literals in ESQL queries.

Answer 2

Unfortunately for me @pmarflee's answer does not work. I've found another solution corresponding to MSDN:

The date part must have the format: YYYY-MM-DD, where YYYY is a four digit year value between 0001 and 9999, MM is the month between 1 and 12 and DD is the day value that is valid for the given month MM.

The time part must have the format: HH:MM[:SS[.fffffff]], where HH is the hour value between 0 and 23, MM is the minute value between 0 and 59, SS is the second value between 0 and 59 and fffffff is the fractional second value between 0 and 9999999. All value ranges are inclusive. Fractional seconds are optional. Seconds are optional unless fractional seconds are specified; in this case, seconds are required. When seconds or fractional seconds are not specified, the default value of zero will be used instead.

There can be any number of spaces between the DATETIME symbol and the literal payload, but no new lines.

DATETIME'2006-10-1 23:11'
DATETIME'2006-12-25 01:01:00.0000000' -- same as DATETIME'2006-12-25 01:01'

And code itself:

DateTime dateTimeValue = DateTime.Now;
// ESQL DATETIME format MUST be <yyyy-MM-dd HH:mm> format                                 
_Query = _Query.Where(string.Format("(it.StartDate >= DATETIME'{0:yyyy-MM-dd HH:mm}')",dateTimeValue);