Working with dates in W2UI and MySQL

Question

I am using MySQL which stores dates in the format 'yyyy-mm-dd'.

I have a layout where one section shows a grid. The records in this grid display the date the record was first created. I need the date to be in the format of 'mm/dd/yyyy'.

This is how the date field in the grid is setup:

{ field: 'incident_date', caption: 'Incident Date', size: '150px', searchable: true, type: 'date', format: 'mm/dd/yyyy' },

I also have a form that opens for the user to edit the record. This is the date field code on that form:

{ name: 'incident_date', type: 'date', format: 'mm/dd/yyyy', required: true },

In w2ui-1.4.2.min.js I have these settings:

"date_format"       : "mm/dd/yyyy",
"date_display"      : "mm/dd/yyyy",

On the form the date field has a popup calendar that appears when this field is selected. When you select a date in the calendar the field gets filled in with the date you selected, in the format of "mm/dd/yyyy".

Now that you have an idea of how things are setup, when you save the record, any date you enter shows up in the grid as '0000-00-00'. if you use phpMyAdmin to look at the actual data in the table the date stored is '0000-00-00'

What I want is for the date to be displayed in the grid and in the form in the format of 'mm/dd/yyyy'. How can I accomplish this?

Thanks, TD

Answer 1

For anyone who may have the same problem, the easiest way to fix this problem is to return your date from the server in mm-dd-yyyy format. For instance, if using MySQL you can use the following function in your SQL SELECT statment, date_format(yourDate, '%m-%d-%Y').

The other fix is to modify w2ui.js. In my opinion the date formatting in w2ui ver. 1.5 does not work correctly. The problem is with w2utils.formatters for dates. It uses the same date format for checking to see if it is a valid date as it does to format the date. So if yyyy-mm-dd is passed into params, it uses that to see if it is a valid date and fails. I have made some changes that worked for me and are listed below.

// MODIFIED LOCALE TO USE yyyy-mm-dd DATE FORMAT
var w2utils = (function ($) {
var tmp = {}; // for some temp variables
var obj = {
    version  : '1.5.RC1',
    settings : {
        "locale"            : "en-us",
        //"dateFormat"        : "m/d/yyyy",
        "dateFormat"        : "yyyy-mm-dd",
        "timeFormat"        : "hh:mi pm",
        "datetimeFormat"    : "m/d/yyyy|hh:mi pm",
        //"datetimeFormat"    : "yyyy-mm-dd|hh:mi pm",

// MODIFIED DATE FORMATTER.  ALWAYS RETRIEVE DEFAULT DATEFORMAT AND USE WHEN CHECKING FOR VALID DATE.  
w2utils.formatters = {
'date': function (value, params) {
    if (params === '') params = w2utils.settings.dateFormat;  // DELETED
    var defaultFormat = w2utils.settings.dateFormat;  //ADDED 
    if (value == null || value === 0 || value === '') return '';
    //var dt = w2utils.isDateTime(value, params, true);
    //if (dt === false) dt = w2utils.isDate(value, params, true);
    var dt = w2utils.isDateTime(value, defaultFormat, true);  // CHANGED
    if (dt === false) dt = w2utils.isDate(value, defaultFormat, true); // CHANGED
    return '<span title="'+ dt +'">' + w2utils.formatDate(dt, params) + '</span>';
},

Hope this helps someone. I am working on getting these changes added to repository.

Thanks John