Gravatar for michiel.lankamp@winvision.nl

Question by Michiel Lankamp, Oct 16, 2015 12:56 PM

Query on datetime: no results

I just installed the latest version of Coveo for Sitecore (October 2015). We have have some calculated date fields, these fiels should be written in the format yyyy/MM/dd@HH:mm:ssZ, the Z is new in this release (should be added to the release notes).

When I run a query where I use the date in the where statement, like:

items = context.GetQueryable().Where(s => s.TemplateId == INewsConstants.TemplateId && s.NewsDate <= DateTime.UtcNow)

this query gets converted to:

(@fz95xtemplate96374==2f05913650b54aa4a9d7651767d89170) (@fnewsdate96374<=2015/10/16@22:00:00Z)

This query results in zero items. When I run the query in the CES Administration Tool on the index it results in zero items, when I remove the Z from the query it returns items

Any idea why the Z is added in my query??

2 Replies
Gravatar for lbergeron@coveo.com

Answer by Luc Bergeron, Oct 16, 2015 1:27 PM

Hi,

Thank you for your feedback. The Z was added to the date time value so CES can know we are passing UTC time. We also had to change CES's date format string to be able to parse the timezone information from the dates.

Rebuilding the Coveo indexes will fix both the CES fieldsets and the date values on the indexed documents.

Let me know how it works Thanks

Gravatar for michiel.lankamp@winvision.nl

Comment by Michiel Lankamp, Oct 16, 2015 1:35 PM

I deleted my search indexes and field sets but that doesn't work!

Gravatar for michiel.lankamp@winvision.nl

Comment by Michiel Lankamp, Oct 16, 2015 1:39 PM

The Z is added in the where statement of the query, even for created/updated fields. Seems like a linq query adds an additional Z, but Coveo doesn't know how to query using that value

Gravatar for lbergeron@coveo.com

Comment by Luc Bergeron, Oct 16, 2015 1:43 PM

Can you go in the fieldset and check one of the datetime fields such as __smallcreateddate? The Date Format field should contain this value: %Y/%m/%d@%H:%M:%S%G.

The %G is the part used to parse the timezone information.

Gravatar for michiel.lankamp@winvision.nl

Comment by Michiel Lankamp, Oct 16, 2015 1:50 PM

I checked this field and my custom calculated field, the date format is the same as you described.

Gravatar for lbergeron@coveo.com

Comment by Luc Bergeron, Oct 16, 2015 2:31 PM

Did you try to run this query again from the Administration Tool since the indexes were rebuilt?

(@fz95xtemplate96374==2f05913650b54aa4a9d7651767d89170) (@fnewsdate96374<=2015/10/16@22:00:00Z)

Does this query returns the expected results?

@fnewsdate96374<=2015/10/16@22:00:00

When searching documents in the Administration Tool, you can all the field values. To do so, click Details > Fields next to the document. It will present the dates in local time (relative to the CES server).

Can you tell me which value is indexed for the @fnewsdate96374field?

Gravatar for michiel.lankamp@winvision.nl

Comment by Michiel Lankamp, Oct 16, 2015 2:35 PM

(@fz95xtemplate96374==2f05913650b54aa4a9d7651767d89170) (@fnewsdate96374<=2015/10/16@22:00:00Z) doesn't return any results

(@fz95xtemplate96374==2f05913650b54aa4a9d7651767d89170) (@fnewsdate96374<=2015/10/16@22:00:00) does!

The value is: @fnewsdate96374 Date/time 3/2/2015 12:00:00 AM

Gravatar for aaronpaul@ccimail.com

Answer by apaul, Feb 25, 2016 12:28 PM

Anyone have an answer to this post that works? I have the same situation where the query is not returning results when Z is on the end of the date string.

Ask a question