3

I am using the QueryBuilder as following:

var queryString = String.Format(
    "SELECT VALUE e FROM Entity AS e WHERE e.EndDate {0} {1} ",
    operator,
    DateTime.Today.AddYears(1).ToString("d", DateTimeFormatInfo.InvariantInfo)
);
ObjectQuery<Entity> query = new ObjectQuery<Entity>(queryString, Context);

Please note that I extremely simplified this example and that my goal is to build the query as string before creating a QueryBuilder instance. (As long as this is possible)

I already tried the following

...DateTime.Today.AddYears(1)...
...DateTime.Today.AddYears(1).ToString()...
...DateTime.Today.AddYears(1).ToString("yyy\MM\dd")...

which all result in a exception which says that I either cannot compare DateTime with a String or with a Int32.

I'm beginning to asking me if this is possible at all with this approach...

Yuck
  • 49,664
  • 13
  • 105
  • 135
sra
  • 23,820
  • 7
  • 55
  • 89
  • 4
    Any reason you really want to have the whole thing as a string, instead of building it as an `ObjectQuery` with parameters? – Jon Skeet Feb 02 '12 at 15:15
  • According to [documentation](http://msdn.microsoft.com/en-us/library/bb399176.aspx) you need to pass it as `DATETIME'2006-10-1 23:11'` – Raj Ranjhan Feb 02 '12 at 15:23
  • @Jon Skeet No really. The only one is that all queries are written this way and that I need to refactor each completly cause that peace of code is somewhere in the middle and just a small part of a greater WHERE... If so I can do it like `query.Where("entity.EndDate > @dateparam", new ObjectParameter("dateparam", DateTime.Today));` ?? – sra Feb 02 '12 at 15:23
  • I believe you have your answer at below link. http://stackoverflow.com/questions/1671254/datetime-comparison-in-objectquery-where – Ravi Vanapalli Feb 02 '12 at 15:31
  • @Ravia that Stackoverflow question you are referring isn't really helpful – sra Feb 02 '12 at 15:35

1 Answers1

5

In Entity SQL DateTime literals must be expressed in the following format:

DATETIME'2012-02-02 16:26'

where both the date and time parts are mandatory. For example:

"SELECT VALUE e FROM Entity AS e WHERE e.EndDate > DATETIME'2012-02-02 16:26'"
Enrico Campidoglio
  • 56,676
  • 12
  • 126
  • 154
  • 1
    Thank u...working fine... but i have one more question. i am search record with date and time but i want to ignore millisecond of time then how to achieve this? – Rikin Patel Jul 31 '12 at 09:09
  • @PatelRikin In the example above milliseconds are already ignored since they're not part of the selection clause. What's your scenario? – Enrico Campidoglio Aug 01 '12 at 13:52
  • 1
    if date save in db like 2012-02-02 16:26.321 and run above query then this record not fetch. and it only fetch up 2012-02-02 16:25 – Rikin Patel Aug 03 '12 at 04:20