How to do dates in mm/dd/yyyy

belldr

I am new to Datatables, I apologize in advance for not being able to get this myself.

Using MySQL and Datatables latest, I am having a doing round-trip editing of a date, where I want the display format to be mm/dd/yyyy, and the date to be stored in MySQL as a regular datetime column.

Display and sorting of the date FROM the database is FINE, using this “columns” spec for my date field, named expire

,{ "data": "expire", "name": "expire",  "mRender": function ( data, type, row ) {
              return data.substr(5,2)+'/'+data.substr(8,2)+'/'+ data.substr(0,4);
                } 

However, when I go to the bubble Editor, the expire date displays as yyyy-mm-dd, and I don’t know how to fix this (after trying many things), while ensuring that the datepicker calender is in sync with the data.

My editor spec for the field is

            ,{ "label": "Expire"
            , "name": "expire"
            , "type": "date"
            , "fieldInfo": "Enter the expiration date of the Notary seal"
            , "dateFormat": $.datepicker.ISO_8601  // USE THIS to get the right data into the editor environment
            , "dateImage": "../../includes/styles/css/images/calendar_icon.gif"
             } //expire

Using alerts in the Editor.on event, editor.get() tells me I get the correct date in yyyy-mm-dd format, so long as I use the unexpected $.datepicker.ISO_8601 format.
If I change dateFormat to

        , "dateFormat": "mm/dd/yy”

I get the wrong date completely in editor.get()

Questions:

Is editor.on the right event for me to be using? Do I have to be doing any even processing at all?

What is the fix to get the date to display in the editor as mm/dd/yyyy, and have the datepicker calendar in sync with that date?

Thanks for any response….

allan

Using dateFormat: 'mm/dd/yy' as you suggest is correct - however:

The problem I think you are running into is that the data format is actually ISO8601 by default from MySQL. The underlaying data for the cell therefore is ISO8601 which is what is fed to Editor - hence the issue! The render function you are using will effect the display in the table, but not Editor.

So what I would suggest you do is use the getFormatter and setFormatter in the Editor PHP / .NET libraries to format the date into what you want on the client-side. This example shows that in action.

Then use this plug-in to enable correct client-side sorting of the date data.

The result is that the mm/dd/yyyy format you want is the only format you have to deal with at the client-side. The Editor libraries will handle the formatting for you.

Allan