CODEWITHSUNDEEP
restsharepoint
cubanGuy
cubanGuy

Reputation: 1296

Using REST to filter Share Point date field

I need to filter the date in wish an item was received in a SharePoint list using REST. I have it partially working, but I have a problem. I'm using a Datepicker to select the date I'm using as filter, but when the date is picked and converted to an acceptable format using toISOString(), I get an "exact" date (e.g. 2018-06-15T00:00:00.000Z). The issue with that is that the Date portion (2018-06-15) matches OK, but not the Time portion, because the datepicker will always give a different time (HH:MM:SS) than the SharePoint list entry.

This is what I'm using:

... $filter=Date_Received eq datetime'2018-05-11T04:00:00.000Z'

I have several items that were entered on 6/15/2018, but I get no values, because the time they were entered was different (e.g. 2018-06-15T05:00:00Z). Is there a way to use something like substringof to filter dates, or does anyone has a workaround?

Thanks in advance.

Upvotes: 0

Views: 1515

Answers (1)

Mike Smith - MCT
Mike Smith - MCT

Reputation: 1221

Dates in SharePoint are stored in GMT. So the times recorded are London times.

Option 1 - Use ranges:

$filter=Date_Received gt datetime'2018-05-10T20:00:00.000Z' and Date_Received lt datetime'2018-05-11T20:00:00.000Z'

If you are not looking for files near midnight then:

$filter=Date_Received gt datetime'2018-05-11T00:00:00.000Z' and Date_Received lt datetime'2018-05-11T23:59:59.000Z'

 

Option 2 - Use the SharePoint 2010 REST API and date functions:

/sites/yourSite/_vti_bin/listdata.svc/yourList?$filter=year(Date_Received) eq 2018 and month(Date_Received) eq 5 and day(Date_Received) eq 11

This still has an issue for dates around midnight due to GMT.

 

Option 3 - Use SharePoint 2010 REST API with a calculated column:

Add a Calculated column named Date_Received_Text as: =TEXT(Date_Received,"yyyy-mm-dd")

/sites/yourSite/_vti_bin/listdata.svc/yourList?$filter=Date_Received_Text eq '2018-07-08'

Same midnight issue...

 

Note: The 2010 API still works in SharePoint Online.

Upvotes: 1

Related Questions