Datasource Date Format Help

15 posts, 1 answers
  1. Andrew
    Andrew avatar
    29 posts
    Member since:
    Sep 2010

    Posted 10 Jan 2012 Link to this post

    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?

  2. Andrew
    Andrew avatar
    29 posts
    Member since:
    Sep 2010

    Posted 10 Jan 2012 Link to this post

    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!
  3. Kendo UI is VS 2017 Ready
  4. Answer
    Rosen
    Admin
    Rosen avatar
    3234 posts

    Posted 11 Jan 2012 Link to this post

    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
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  5. Andrew
    Andrew avatar
    29 posts
    Member since:
    Sep 2010

    Posted 11 Jan 2012 Link to this post

    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?
  6. Andrew
    Andrew avatar
    29 posts
    Member since:
    Sep 2010

    Posted 11 Jan 2012 Link to this post

    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;
       }
     
  7. Anibal
    Anibal avatar
    6 posts
    Member since:
    Feb 2012

    Posted 06 Apr 2012 Link to this post

    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
  8. Daniel
    Daniel avatar
    10 posts
    Member since:
    Jun 2011

    Posted 19 Apr 2012 Link to this post

    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
  9. Mat
    Mat avatar
    13 posts
    Member since:
    Mar 2012

    Posted 03 May 2012 Link to this post

    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?
  10. Daniel
    Daniel avatar
    10 posts
    Member since:
    Jun 2011

    Posted 03 May 2012 Link to this post

    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);
        }
    });
  11. Mat
    Mat avatar
    13 posts
    Member since:
    Mar 2012

    Posted 04 May 2012 Link to this post

    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.
  12. Justin
    Justin avatar
    3 posts
    Member since:
    Feb 2012

    Posted 19 Jun 2012 Link to this post

    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)  

  13. Swarup
    Swarup avatar
    17 posts
    Member since:
    Apr 2012

    Posted 21 Jun 2012 Link to this post

    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
  14. Chanaka
    Chanaka avatar
    14 posts
    Member since:
    Jan 2012

    Posted 30 Aug 2012 Link to this post

    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 
  15. Jun
    Jun avatar
    5 posts
    Member since:
    Jul 2012

    Posted 02 Oct 2012 Link to this post

    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>    
  16. Chanaka
    Chanaka avatar
    14 posts
    Member since:
    Jan 2012

    Posted 03 Jan 2013 Link to this post

    @Jun

    Use the parameterMap after the Data-source 

     or try

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

Back to Top
Kendo UI is VS 2017 Ready