Checked null with dateTime in REST API SharePoint

Question

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

Answer 1

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"
            }
        ]
    }
}

Answer 2

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

$filter=DateField ne null

Answer 3

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?

Answer 4

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.

Answer 5

"$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.

Answer 6

Anyone using OData to get data from project online can use default datetime value to filter for null date in task field.

&$filter=BaselineFinish eq datetime'0001-01-01T00:00:00' or BaselineStart eq datetime'0001-01-01T00:00:00'

null is not supported in this scenario.