7

I want to get all article pages which Article Date is not null. so I have try to do by this way.

 var ArticlePageContentTypeID = "0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D";
 var url = "/_api/web/lists/getbytitle('Pages')/items?&$select=ArticleStartDate&$orderby=ArticleStartDate desc&$filter=startswith(ContentTypeId,'" + ArticlePageContentTypeID + "') and ArticleStartDate ne null";
 jQuery.ajax({
   url: url,
   type: "GET",
   headers: { "Accept": "application/json;odata=verbose" },
   success: function(){},
   error: function(){}

  });

but it does not working it. error mention that

"String was not recognized as a valid DateTime."

I have try with replacing with empty string but it no luck to be found.

Please help me if you are face this problem

6

To the best of my knowledge the REST API does not support OData filters on null date values.

There is an MSDN forums post with someone having the same issue and a member of the SharePoint team mentions that it is a known issue and they are working on it.

The current implementation of REST does not support null values for filtering on list item queries. But as this is a desirable feature, there is a bug tracking it and we are discussing on when we add this support.

But you could use a CAML query.

Here I am searching for items in a list called Test List, and I am posting the following CAML:

<View>
   <Query>
      <Where>
         <IsNull>
            <FieldRef Name='DateCompleted' />
         </IsNull>
      </Where>
   </Query>
</View>

$.ajax({
    type: "POST", 
    headers: { 
            "accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
        }, 
    url: 'https://kaucher.sharepoint.com/_api/web/Lists/GetByTitle(\'Test List\')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><IsNull><FieldRef Name=\'DateCompleted\' /></IsNull></Where></Query></View>"}', 
    success: function(data){console.log(data); },
    failure: function(data){console.log(data); } 
});

That returned the following from my list:

{
    "d": {
        "results": [
            {
                "__metadata": {
                    "id": "3e55bc9c-146b-4319-9627-33338da73bc9",
                    "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-bbf4-99fd-a9a1-4634afe328ac')/Items(1)",
                    "etag": "\"1\"",
                    "type": "SP.Data.Test_x0020_ListListItem"
                },
                "FirstUniqueAncestorSecurableObject": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/FirstUniqueAncestorSecurableObject"
                    }
                },
                "RoleAssignments": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/RoleAssignments"
                    }
                },
                "AttachmentFiles": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/AttachmentFiles"
                    }
                },
                "ContentType": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/ContentType"
                    }
                },
                "FieldValuesAsHtml": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/FieldValuesAsHtml"
                    }
                },
                "FieldValuesAsText": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/FieldValuesAsText"
                    }
                },
                "FieldValuesForEdit": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/FieldValuesForEdit"
                    }
                },
                "File": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/File"
                    }
                },
                "Folder": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/Folder"
                    }
                },
                "ParentList": {
                    "__deferred": {
                        "uri": "https://somedomain.com/_api/Web/Lists(guid'efd16a43-aaf4-44fd-a9a1-4634afe328ac')/Items(1)/ParentList"
                    }
                },
                "FileSystemObjectType": 0,
                "Id": 1,
                "ContentTypeId": "0x010017F678F8FDCCC8468A19A6A067EFF04D",
                "Title": "Test ITem",
                "Test_x0020_Item": "Enter Choice #1",
                "DueDate": "2014-02-21T00:00:00",
                "DateCompleted": null,
                "ID": 1,
                "Modified": "2014-02-14T19:10:59",
                "Created": "2014-02-14T19:10:59",
                "AuthorId": 12,
                "EditorId": 12,
                "OData__UIVersionString": "1.0",
                "Attachments": false,
                "GUID": "e5f4e83e-3bfd-47fe-a5c9-41ef51c71d29"
            }
        ]
    }
}
  • You can filter DateTime based on null values. Check my answer. – Vardhaman Deshpande Feb 26 '14 at 6:46
  • I've added some information regarding this being a bug. It may have been corrected in an update. SP1 might fix this... – Robert Kaucher Feb 26 '14 at 13:06
  • Nearly a year later and post SP1, I still get this error. – Robert Kaucher Dec 9 '14 at 15:57
  • 1
    I've struggled with this issue for years, but I'm on SP 2016 (not sure of CU, but it's not the first one) and this has finally been addressed on-prem! – Dinerdo Jun 26 '18 at 16:28
3

You can filter out list items with null columns in SharePoint 2016 (definitely after feature pack 1) using REST.

This filter syntax works:

$filter=DateField eq null
2

I was able to filter out null dates with a rest query by filtering on dates greater than 1/1/1900...

$filter=DueDate gt datetime'1900-01-01T00:00:00'

they may have fixed the null thing on Office365, but my on premises install still has the issue.

2

"$filter=DueDate gt datetime'1900-01-01T00:00:00'" didnt work for me on-premise. For some reason it gave me just all results.

For textfields this worked for me: "$filter=SomeTextField gt 0" This gave me all results where SomeTextField not equals null But visa versa "$filter=SomeTextField lt 0" This gave me no results at all

I have no explanation for this, but you can use "$filter=SomeTextField gt 0" to filter out the null-records on textfields.

  • Your answer seems a bit of unexplained, Can you please elaborate with explanation. – Asad Refai Sep 4 '15 at 10:17
1

The following query worked perfectly for me:

https://<siteurl>/_api/web/lists/getbytitle('MyLibrary')/items?$select=Title&$filter=Date_x0020_of_x0020_Issue ne null

Can you check if you are using the exact internal name of the DateTime field? Also for test purposes, can you check without the startswith filter in your query?

  • Yes. it seem to worked my app on Office 365 site, but when i am moved to On-premise server 2013, I got same error mention on post. – Jignesh Rajput Feb 26 '14 at 7:30
  • I recall this being an issue in beta and RTM. Maybe it was fixed with an update? – Robert Kaucher Feb 26 '14 at 12:40
  • Quite Possible. – Vardhaman Deshpande Feb 26 '14 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.