Datasource Date Format Help

[Andrew]

I am setting up a page using MySQL/PHP on the back end, and using a DataSource + Kendo Grid for display.   Having odd issues with date fields I was hoping someone could shed some light on.  

I have my transport set up like this:

transport: {
 read: {
                url: "services/crud/tbl-read.php", 
                dataType: "JSON"
              },
              create: {
                url: "services/crud/tbl-add.php", 
                type: "POST"
              },
              update: {
                url: "services/crud/tbl-edit.php",
                type: "POST"
              } ,
              ...
and the schema/model setup like this:

     datefld : {  editable: true, type: "date"},

and I have my Grid column display setup like this (using date format to match):

columns:[
 { field: "tbl_id", title: "ID", width: "50px" },
       ...
 { field: "datefld", title:"My Date Field",  format: "{0:yyyy-MM-dd}" },

       ...

This retrieves and displays the date field properly.  Grid also shows the built in date picker and so forth.

Where I run into trouble is when I ADD or EDIT.   On an Add, the request sent to my PHP script DOES NOT include a value pair for "datefld" (!!??!!).    I get value pairs for all the other (non-date) fields, not the date field.   Similarly on an EDIT, if I edit the date (using the grid's supplied date picker)  the PHP request does not include the value pair for the date field.   Oddly enough, if I edit the record in the grid BUT only change one of the other (string) fields, I DO GET a value pair for the date field (I am assuming since it was untouched by the edit).   As a test, I toggled the schema TYPE clause from "date" to "string" and everything works as it should (data displays, value pairs are included, but I obviously don't get a date picker in the grid).    

A further issue with this setup is that built in grid filtering does not work.   If I attempt to filter on the date field (with TYPE set to "date") I am unable to get the filter to apply - I get a spinning wait/progress/hourglass deal.   Obviously with type set to string I can filter, but not as a date as desired.  

My best guess is that Kendu does not like the date format, but I have found limited information in the forums and documentation about date formats (and none relating to MySQL, PHP and CRUD).   

Anyone have similar issues?  Anyone getting datefields to work properly with MySQL/PHP/CRUD?

[Andrew]

Followup...

I did some debugging and added this:

alert(options.datefld);

to the parameterMap() of my datasource just to see what was going on.   In my example (again, I am using a date format of yyy-MM-dd, or '2012-01-26' ), if I enter 2012-01-26 into the grid (either typing or using the date picker), when the ParameterMap() fires to update the server the value is not '2012-01-26', but rather a fully qualified 'Thu Jan 26 2012 00:00:00 GMT-0800 (Pacific Standard Time)'. 

So is it up to the coder to implement custom date reformatting for the datasource on all date fields?? Or is there something more basic about handling these date types that I'm missing?  

Cheers!

[Rosen]

Hi Andrew,

Indeed, the dates used in the DataSource are JavaScript Date objects. Therefore, if you need to format the date values to a different format when send to the server you should use paramaterMap to convert the values, or you should make your server aware of the JavaScript Date format.

Regards,
Rosen
the Telerik team

[Andrew]

Understood about using ParameterMap when sending to the server.   I am thinking my related "filter" issue is the same deal - the datasource is not properly mapping my date value "2012-01-26" in the JSON Read to a Javascript date.   Information in the documentation is sketchy, but some forum posts suggest the proper place to do this is a schema.parse section of my datasource.   True?

[Andrew]

Got this to work.  For posterity, I am doing the below.  Note I opted to use DatesJS (www.datesjs.com) for formatting ease.

My transport.parameterMap now contains:

 parameterMap: function(options, operation) {
...
     var d = new Date(options.datefld);
         options.datefld = d.toString("yyyy-MM-dd"); .
...

and my schema.parse now contains:

 parse : function(data) {
    $.each(data.myrecs, function(i, val){
      val.datefld = Date.parse(val.datefld);
     });
     return data;
   }
 

[Anibal]

Hi Andrew,

Thank you!  This is critical stuff.

Any interface has to manage schema.parse / dataSource.parameterMap.

I started trying to match the PHP/Javascript date definition as "D M d Y h:i:s T"... but when I combined it with the globalization .. it changed again!

At the end, schema.parse saved the day. A simple:

parse : function(data) {
                        $.each(data, function(i, val){
                            val.created = Date.parse(val.created);
                        });
                        return data;
                    }


Regards,
Anibal Sanchez - http://www.prieco.com

[Daniel]

Hi Andrew,

I tried your suggestion on a date field but it does not seem to work for me.  Excerpts from my code below.

    transport: {
        read: {
            url: function(options) {
                var url = "http://localhost/TaskRecorder/api/Tasks/" + memberId;
                return url;
            }, type: "GET", dataType: "json"},
        update: {type: "PUT", dataType: "json"},
        destroy: {type: "DELETE", dataType: "json"},
        create: {type: "POST", dataType: "json"},
        parameterMap: function(options, operation) {
            if (operation != "read"){
                var d = new Date(options.TaskDate);
                options.TaskDate = d.toString("yyyy-MM-dd");
                alert("parameterMap TaskDate: " + options.TaskDate);
                return options;
            }
        }
    },                            
    batch: false,
    schema: {
        model: {
            id: "TaskId",
            fields: {
                TaskId: { type: "number", editable: false, nullable: true },
                MemberId: { type: "number" },
                TaskDate: { type: "date" },
                TaskDescription: { type: "string", validation: { required: true } },
                Hours: { type: "number" },
                Minutes: { type: "number" },
                SubCategoryName: "SubCategoryName"
            }                                            
        },
    },

The alert message still shows the "long" date.

Fiddler confirms this.


And my ASP.Net Web API method does not seem to recognize the date parameter.


Did I miss anything?

Cheers,
Dan

[Mat]

I'm really struggling to get this working. I'm reading/saving to/from a MySQL database, so the raw dates are in YYYY-MM-DD format. I've got them to display correctly in the grid using the following scheme.parse:

parse: function(data) { 
$.each(data.data, function(i, val){
val.start_date = Date.parse(val.start_date);
});
return data; 
}

In the grid column definition I can now change the display format using something like:
{ field: 'start_date', title: 'Start Date', format: "{0:dd/MM/yyyy}" }

so the dates are clearly being converted to date objects.

However, if I click on a date to edit it, I get the following error in the console:
Uncaught SyntaxError: Unexpected number 

I haven't even got the the point of saving back into the database yet, but I think I'll be okay based on the above info.

Can anyone help with this?

[Daniel]

Hi Mat,

I finally got it working with these code snippets. I receive my data for the date in this format: TaskDate=/Date(1330513200000+1300)/ .

.....
parameterMap: function(options, operation) {
        if (operation != "read"){
            var d = new Date(options.TaskDate);
            options.TaskDate = kendo.toString(new Date(d), "MM/dd/yyyy");
            return options;  //return kendo.stringify(options);
        }
    }

.....
parse : function(data) {
            $.each(data, function(i, val){
                val.TaskDate = new Date(parseInt(val.TaskDate.substr(6)));
            });
            return data;
        }

.....
$("#grid").kendoGrid({
    dataSource: sharableDataSource,
    toolbar: [{template: $("#template").html()}, "create"],
    height: 400,
    columns: [    {field: "TaskDate", title: "Date", width: "60px", template: '#= kendo.toString(TaskDate, "dd/MM/yyyy")#'},
                {field: "TaskDescription", title: "Task Description", width: "120px"},
                {field: "Hours", title: "Hours", width: "50px", format: "{0:n0}"},
                {field: "Minutes", title: "Minutes", width: "50px", format: "{0:n0}"},
                {field: "SubCategoryName", title: "Classification", width: "120px", editor: subCategoryDropdownEditor},
                {command: ["edit", "destroy"], title: " ", width: "140px" }],
    editable: "inline",
    selectable: "multiple",
    save: function(e) {
        e.model.set("MemberId", memberId);
    }
});

[Mat]

Daniel -- that's brilliant. It's working perfectly now.

Thanks very much for your help.

Edit: Oops. I spoke just a tiny bit too soon. I'm 90% there. I can load and save the dates and the datepicker doesn't throw any errors. The only slight niggle now is that the date switches from my defined format: dd/MM/yyyy (UK format) to MM/dd/yyyy (US format) when I click in the box to edit. It switches back again after clicking a date in the datepicker.

This isn't a major concern for the moment (I'm just glad to be able to save changes), but if anyone has a solution it would be much appreciated.

Thanks again.

[Justin]

Oh, the irony...I came across this post only AFTER I had beat up on the PHP and datasource and rest of the columns in the specification in order to realize the nature of the issue.

What's going on is this: 

<b style="font-family: 'lucida sans unicode', 'lucida grande', arial, helvetica, sans-serif; line-height: 19px;">{ field: 'start_date', title: 'Start Date', format: "{0:dd/MM/yyyy}"}</b> 

will cause the cells in the column to change their associated JAVASCRIPT object to a string with the given format

<i style="font-family: arial, verdana; font-size: 12px; line-height: 16px;"><b>{field: "TaskDate", title: "Date", width: "60px", template: '#= kendo.toString(TaskDate, "dd/MM/yyyy")#'}</b></i> 

will cause the cells in the column to change their associated HTML to a string with the given format

to restate: the first breaks the datepicker by giving it a non-"date" object, the second causes the html rendered to screen in non-edit mode to appear as a formatted string.

@ADMIN: if this behavior is intentional, it should be made clear in the documentation (a separate page for each option would be nice)  

[Swarup]

Hi All,

Can anybody please help me in getting the datas from the mysql database and show it in my application..for the testing purpose i am using my sql and trying to retrive the data using php for an application using Kendo UI.I have created a new thread earlier,however i did not get a reply so i have to post my Question here.

i am giving my code here..the output i am getting is only the grid format with its field names.

Parvathi K [12:28 PM]:
 
<!DOCTYPE html>

<html>

 

<head>  

<link href="D:/kendoui.complete.2012.1.322.trial/styles/kendo.common.min.css" rel="stylesheet" />

<link href="D:/kendoui.complete.2012.1.322.trial/styles/kendo.default.min.css" rel="stylesheet" />

<script src="D:/kendoui.complete.2012.1.322.trial/js/jquery.min.js"></script>

<script src="D:/kendoui.complete.2012.1.322.trial/js/kendo.all.min.js"></script>

    

</head>



<body>



<link href="D:/kendoui.complete.2012.1.322.trial/examples/web/grid/index.html"/>

<div id="example" class="k-content">            

<div id="clientsDb">              

<div id="grid">

</div>            

</div>

            

<style scoped>

               

 #clientsDb {                   

 width: 692px;                  
  height: 393px;                    
 margin: 30px auto;                   
 padding: 51px 4px 0 4px;                 
 background: url('D:/kendoui.complete.2012.1.322.trial/examples/content/web/grid/clientsDb.png') no-repeat 0 0;              
  }
           
 </style>

          
<script>

    $(document).ready(function () {
    var data={};

    var modelid1=["m001","m002","m003","m004"];
    var modelname1=["red","black","blue","black"];
    var category1=["m1","m2","m3","m4"];

    var generaterow=function(){
    var row={};
    var modelidindex   = Math.floor(Math.random()  *  modelid1.length);
          var modelnameindex = Math.floor(Math.random() *  modelname1.length);
          var categoryindex  = Math.floor(Math.random() *  category1.length);


    row["model_id"]=modelid1[modelidindex];
    row["model_name"]=modelname1[modelnameindex];
    row["category"]=category1[categoryindex];
    return row;
}

        // prepare the data
        var source =
        {
            datatype: "json",
        

            datafields: [
               { name: 'CompanyName'},
                 { name: 'ContactName'},
                 { name: 'ContactTitle'},
                 { name: 'Address'},
                 { name: 'City'},
            ],
url:'data.php',

          
};

        $("#grid").kendoGrid(
        {
            datasource: source,
           
            columns: [
             { title: 'Company Name', datafield: 'CompanyName', width: 250},
               { title:'ContactName', datafield: 'ContactName', width: 150 },
               { title:'Contact Title', datafield: 'ContactTitle', width: 180 },
               { title:'Address', datafield: 'Address', width: 200 },
               { title:'City', datafield: 'City', width: 120 }
              
          ]
        });
    });
</script>
</div>
</body>
</html>

Thanks,
Swarup

[Chanaka]

It is a Simple  Answer Use parameterMap and column format

parameterMap: function (options, operation) {
                            if (operation != "read") {
                                var d = new Date(options.Date);
                                options.Date = d.toString("yyyy-MM-dd");                            
                                return options;
                            }
                        }

{ field: "Date", title: "Date ", type: "date", format: "{0:dd/MM/yyyy}" }

result : 30/08/2012

Hope this will help all of you all 

[Jun]

I'm novice in jquery and kendoui. I can't update field Data1  type: "date".  Wthat I did wrong?
<script type="text/javascript">
   
        $(function() {
            $("#grid").kendoGrid({
                dataSource: {
                    transport: {
                        
                        read: {url: "data/employee.php", type:"GET"},
                        update: {url: "data/employee.php",  type:"POST"},
                        parameterMap: function(options, operation) {
                        if (operation != "read"){
                            var d = new Date(options.Data1);
                            options.Data1 = kendo.toString(new Date(d), "yyyy-MM-dd");
                           // return options;
                            return kendo.stringify(options);
                        }}
                        },
                    
                    
                    
                    },
                    pageSize: 10,
                    schema: {
                        data: "results",
                        total: function(data) {
                        data = data.results || data;
                        return data.length;
                        },
                        model: {
                        id: "EmployeeId",
                        fields: {
                        EmployeeId: { editable: false },
                        FirstName: { editable: true},
                        LastName: { editable: true},
                         Data1: {  type: "date"},
                        Salario: { type: "number",editable: true }
                        }
                        }},
                        parse : function(data) {
  $.each(data, function(i, val){
                                    val.Data1 = new Date(parseInt(val.Data1.substr(10)));
    });
                               return data;
  }
                        },
                
                columns: [{ field: "EmployeeId", title: "Codigo", width: 100 },
                         { field: "FirstName" },
                         {field: "LastName" },
                         {field: "Data1",title:"Data",format:"{0:yyyy-MM-dd}"},
                         {field: "Salario", title:"Salario", format:"{0:n2}"},
                         { command: ["edit"], title: "Editar/Salvar", width: "180px" }],


                
                serverPaging: true,
                pageable: true,
                scrollable: true,
                sortable: true,
                height: 425,
                editable:"inline",
                navigable: true
        });
        });
        
    </script>
<body> 
        <div id="grid"></div>
/body>    

[Chanaka]

@Jun

Use the parameterMap after the Data-source 

 or try

{ field: "Date", title: "Date", type: "date", format: "{0:yyyy-MM-dd}"},