Using REST call and Datatables plugin to retrieve SharePoint Date field

Question

I'm having an issue with a REST call that's giving me some headaches. This is the code I'm using:

function LoadAIP(state)
{
var call = $.ajax({
url: "https://xxxx/xxxx/_vti_bin/listdata.svc/AIPList?  
$select=ROCode,AIP,NotificationDate&$filter=(ROCode eq '"+state+"')&$top=5000",

type: "GET",
dataType: "json",
headers: {
Accept: "application/json;odata=verbose"
}
});

call.done(function (data,textStatus, jqXHR){
$('#example').dataTable( {
"bJQueryUI": true,
"sPaginationType": "full_numbers",
 "aaData": data.d.results,
"aoColumns": [
{ "mData": "ROCode" },
{ "mData": "AIP" },
{ "mData": "NotificationDate" }],"bRetrieve": true,

"sDom": '<"H"Tfr>t<"F"ip>',
"oTableTools": {
"aButtons": [ "xls"], 
"sSwfPath": ../js/datatables/TableTools/media/swf/copy_csv_xls_pdf.swf",
"bFilter": true}
} );
});
call.fail(function (jqXHR,textStatus,errorThrown){
alert("Error retrieving Tasks: " + jqXHR.responseText);
});
}

It works except with one of the columns (NotificationDate). This field, as the name says it it a date field. When I make the call instead of showing a date, as would expect, it shows something else. For example, one of the rows it should show 4/9/2014, but instead shows this: /Date(1397001600000)/. I had a similar problem some time ago with XSLT and I used the ddwrt:DateTimeTick function to fix it in a Data view web part, but in this case I don't know what to do. Thanks!

Community · Accepted Answer · 2017-05-23 12:41:28Z

2

According to MSDN DateTime object:

represented in JSON as "/Date(number of ticks)/". The number of ticks is a positive or negative long value that indicates the number of ticks (milliseconds) that have elapsed since midnight 01 January, 1970 UTC. The maximum supported date value is MaxValue (12/31/9999 11:59:59 PM) and the minimum supported date value is MinValue (1/1/0001 12:00:00 AM).

To convert JSON DateTime object into DateTime the following function could be used:

function dateFromJSON(json)
{
    return new Date(parseInt(json.replace(/\/Date\((\d+)\)\//gi, "$1")));
}

Usage:

var createdDate = dateFromJSON(taskItem.Created);

Please follow this answer for another options.

Or leverage library for parsing, validating, manipulating, and formatting dates like Moment.js:

var createdDate = moment(taskItem.Created); //convert JSON DateTime 
console.log(createdDate.toString());

edited May 23, 2017 at 12:41

CommunityBot

1

answered Apr 24, 2014 at 22:44

Vadim Gremyachev

42.6k3 gold badges88 silver badges169 bronze badges

As I mentioned in my provious comments. I used moment.js. I added another option to datatables(): "fnInitComplete":function() { $('#example thead tr th:eq(2)').each(function(i) { var theCells = $(this).parents('#example') .find('tbody tr td:nth-child(3)');//The column if($(this).html()!=null) { theCells.html(moment(this).format('L')); } . But it shows all the dates as today's date. Thanks!

cubanGuy
– cubanGuy

2014-04-25 17:43:08 +00:00
Commented Apr 25, 2014 at 17:43

Add a comment |

Paul Schaeflein · Accepted Answer · 2014-04-24 15:53:03Z

1

I recommend the moment.js library for handling date processing and formatting in javascript.

answered Apr 24, 2014 at 15:53

Paul Schaeflein

5,0481 gold badge22 silver badges29 bronze badges

I went ahead and used moment.js. It partically works. The problem is that not is showing me all dates as "today's date": 4/25/2014. I'm using: moment(string).format('L'). Any ideas?

cubanGuy
– cubanGuy

2014-04-25 17:37:50 +00:00
Commented Apr 25, 2014 at 17:37

Add a comment |

cubanGuy · Accepted Answer · 2014-06-20 15:30:25Z

I found out that all that is needed is to add this to the column containing the Jason Date:

function LoadAIP(state) {
var call = $.ajax({
    url: "https://xxxx/xxxx/_vti_bin/listdata.svc/AIPList?  
$select=ROCode,AIP,NotificationDate&$filter=(ROCode eq '" + state + "')&$top=5000",

    type: "GET",
    dataType: "json",
    headers: {
        Accept: "application/json;odata=verbose"
    }
});

call.done(function (data, textStatus, jqXHR) {
    $('#example').dataTable({
        "bJQueryUI": true,
            "sPaginationType": "full_numbers",
            "aaData": data.d.results,
            "aoColumns": [{
            "mData": "ROCode"
        }, {
            "mData": "AIP"
        }, {
            "mData": "NotificationDate",
            "fnRender": function (obj, val) {
                var dx = new Date(parseInt(val.substr(6)));
                var dd = dx.getDate();
                var mm = dx.getMonth() + 1;
                var yy = dx.getFullYear();

                if (dd <= 9) {
                    dd = "0" + dd;
                }
                if (mm <= 9) {
                    mm = "0" + mm;
                }
                return dd + "/" + mm + "/" + yy;
            }
        }]
    }], "bRetrieve": true,

        "sDom": '<"H"Tfr>t<"F"ip>',
        "oTableTools": {
        "aButtons": ["xls"],
            "sSwfPath": .. / js / datatables / TableTools / media / swf / copy_csv_xls_pdf.swf ",

"bFilter ": true}
} );
});
call.fail(function (jqXHR,textStatus,errorThrown){
alert("
        Error retrieving Tasks: " + jqXHR.responseText);
});
}

MAZDA · Accepted Answer · 2017-06-28 09:48:09Z

I used in the rest call

**Date Variable is: Created **

var call = $.ajax({
            url: "../_api/Web/Lists/GetByTitle('NewUser')/items?$filter=(Created%20ge%20datetime'"+FromDate+"')%20and%20(Created%20le%20datetime'"+ToDate+"' and Title eq '"+epf+"' )&$top=5000",
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }

            });

  call.done(function (data,textStatus, jqXHR){
        $('#example').dataTable({
            "bDestroy": true,
            "bProcessing": true,
            "aaData": data.d.results,
            "aLengthMenu" : [
             [50,100],
             [50,100]
            ],
             dom: 'Bfrtip',
            buttons: [
                'copy', 'csv', 'excel'
            ],

            "aoColumnDefs": [{ "bVisible": false  }],
            "aoColumns": [
                { "mData": "ID" },
                { "mData": "Title" },
                { "mData": "EmployeeName" },
                { "mData": "Department1" },
                { "mData": "ServicingAt" },
                { "mData": "TestField" }, 
                { "mData": "BranchCode" },   
                { "mData": "Created" ,"render": function (data, type, row) {
        data = moment(data).format('DD MMM YYYY');
        return data;
    }

Stack Exchange Network

Using REST call and Datatables plugin to retrieve SharePoint Date field

4 Answers 4

Your Answer

Linked

Hot Network Questions

Using REST call and Datatables plugin to retrieve SharePoint Date field

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions