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.

pmarflee · Accepted Answer · 2009-11-04T09:51:06.853

5

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.

edited Nov 04 '09 at 09:51

answered Nov 04 '09 at 09:22

pmarflee

3,428
20
21

still doesnt work, that gives me this error "The argument types 'Edm.DateTime' and 'Edm.String' are incompatible for this operation., near WHERE predicate, line 6, column 15." – Juvil Nov 04 '09 at 09:28
@Juvil: I've updated the code snippet to include the DATETIME qualifier which is needed to distinguish date/time literals from string literals. – pmarflee Nov 04 '09 at 09:52

score 5 · Answer 2 · answered Feb 13 '12 at 16:40

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);

@Anatolli Can you please help me with this http://stackoverflow.com/questions/22247016/edm-datetime-coversion-javascript — Sangamesh Hs, Mar 07 '14 at 10:21

ObjectQuery, passing datetime in Where clause filter

2 Answers2