Date formatting using inline editing with date picker.

bbrindza

I trying to figure out how handle date formats using DataTable Editor, with inline datepicker.

I am using a SSP script to retrieve and update my database.

The field I need is stored in the DB as YYYMMDD format (text).

So I need to achieve the following...

1. Convert the date from YYYYMMDD to mm/dd/yyyy for the front-end DataTable .
2. When user changes date using datepicker inline, I need to update the back-end database with the YYYMMDD format.

This would also apply to the DataTable Editor form field as well.

SSP

<?php
// DataTables PHP library
require( $_SERVER['DOCUMENT_ROOT']."/DataTables_Editor/php/DataTables.php" );
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'NWxxxx.PAYxxxx', 'HREMxx')
->debug(true)
    ->fields(
        Field::inst('HRLxx'),                   
        Field::inst('HRLOxx' 
         Field::inst('HRNOxx'),  
        Field::inst('HRNAMxx'),                    
        Field::inst('HREFDTxx')       // SALARY EFFECTIVE DATE - Formatted YYYMMDD
)
    ->process( $_POST )
    ->json();

JS

<script type="text/javascript" src="/js/jquery-current.min.js"></script>
<script type="text/javascript" src="/jquery-ui-1.12.0/jquery-ui.min.js"></script>
<script type="text/javascript" src="/js/jquery.dataTables.min.js"></script>
<!-- <script type="text/javascript" src="/js/dataTables.fixedHeader.min.js"></script> -->
<script type="text/javascript" src="/js/dataTables.fixedColumns.min.js"></script>
<script type="text/javascript" src="/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="/js/dataTables.select.min.js"></script>
<script type="text/javascript" src="/DataTables_Editor/js/dataTables.editor.min.js"></script>
<script type="text/javascript" src="/js/dataTables.rowGroup.min.js"></script> 
<script type="text/javascript" src="/DataTables_Editor/js/editor.display.js"></script> 

<script type="text/javascript">

var editor; //global for the submit and return data rendering
var table; // global for the data table
//****************************************************************************
//** $(document).ready(function()
//****************************************************************************
$(document).ready(function() {
  editor = new $.fn.dataTable.Editor( {
             ajax: "ssp_script.php",
             table: "#approvalTable",
             fields: [
                 {label: "Employee Name",
                      name: "HRNAMxx",
                      type:  "display",
                     },
                     {label: "Employee Number",
                      name: "HRNOxx",
                     },
                      {label: "Effective Date",
                      name: "HREFDTxx",          // SALARY EFFECTIVE DATE - Formatted mm/dd/yyyy 
                    }, 
                     
                 ]
    } );
  // Activate an inline edit on click of a table cell
  $('#approvalTable').on( 'click', 'tbody td:not(:first-child):not(\'.live\')', function (e) {
      editor.inline( this, {
          onBlur: 'submit'
      } );
  } );

   table = $('#approvalTable').DataTable( {
        lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]], 
        },
        scrollX: true,
        dom: "Bfrtip",
        ajax: "ssp_HourlySalaryIncreaseApproval.php",
        type: 'POST',
        order: [[2, 'asc']],
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
             },
            { data: "HRLxx"},
            { data: "HRLOxx"},
            { data: "HRNOxx" },
            { data: "HRNAMxx" }, 
            { data: "HREFDTxx" },  // SALARY EFFECTIVE DATE - Formatted mm/dd/yyyy
         ],
                  select: {
                      style:    'os',
                      selector: 'td:first-child'
                  },

                   buttons: [
                 //     { extend: "create", editor: editor },
                      { extend: "edit",   editor: editor },
                  //    { extend: "remove", editor: editor }
                  ]
    } );

});//END $(document).ready(function() 
</script>

allan

On the client-side always use your formatted date, on the server-side use get and set formatters to convert to and from the date format you want. There is an example of that here (click the "Server script" tab to see the PHP code).

The PHP formatter documentation also has more details about date formatters.

Allan

bbrindza

I tried your suggestion and here is my scripts. The getFormatter is working , but not the setFormatter. I receive the following error. Is that my DB date field is text?

========
{fieldErrors: [{name: "HRSEDT", status: "Date is not in the expected format"}], data: [], debugSql: []}
data: []
debugSql: []
fieldErrors:[{name: "HRSEDT", status: "Date is not in the expected format"}]
0:{name: "HRSEDT", status: "Date is not in the expected format"}
name:"HRSEDT"

status:"Date is not in the expected format"

        Field::inst('HRSEDT')   // SALARY EFFECTIVE DATE - Formatted in DB as YYYMMDD (text)
        ->validator('Validate::dateFormat', array(
            'format' => 'Ymd'
        ) )
        ->getFormatter('Format::datetime', array(
            'from' => 'Ymd',
            'to' =>   'm/d/Y'
        ) )
        ->setFormatter('Format::datetime',array(
            'MM/DD/YYYY',
            'Ymd'
        ) ),
} );

                     {label:  'Effective Date',
                     name:   'HRSEDT',
                     type:   'datetime',
                     def:    function () { return new Date(); },
                     format: 'MM/DD/YYYY',
                    }, 
} );

allan

Your setFormatter for datetime doesn't had the from and to parameters like you've got for the getFormatter. With those missing, it won't work.

Allan

bbrindza

Add the to and from still errors out .

{"fieldErrors":[{"name":"HRSEDT","status":"Date is not in the expected format"}],"data":[],"debugSql":[]}

 Field::inst('HRSEDT')   // SALARY EFFECTIVE DATE - Formatted in DB as YYYMMDD (text)
        ->validator('Validate::dateFormat', array(
            'format' => 'Ymd'
        ) )
        ->getFormatter('Format::datetime', array(
            'from' => 'Ymd',
            'to' =>   'm/d/Y'
        ) )
        ->setFormatter('Format::datetime',array(
            'from' => 'MM/DD/YYYY',
            'to' =>  'Ymd'
        ) ),
        

bbrindza

May need to note that I am using the latest version of moment.js for date.time.

allan

The get and set formatters are using different values there. Indeed YYYY in PHP's date() function is 16 digits (i.e. 4 digit 4, four times).

I'd suggest that they should be using the same format.

Allan

bbrindza

Got it. Thanks for the update.

bbrindza

OK I tried this and it still is throw an error. "Date is not in the expected format"

data[row_793][HRSEDT]:07/04/2016

{"fieldErrors":[{"name":"HRSEDT","status":"Date is not in the expected format"}],"data":[],"debugSql":[]}

This field on the front end JS was change to display only. The user cannot edit now.

     {label: "Effective Date",
      name:  "HRSEDT",
      type:  "display",
       },

 { data: "HRSEDT" }, 

Here s the PHP

        Field::inst('HRSEDT')   // SALARY EFFECTIVE DATE - Formatted in DB2 as YYYMMDD (text)
         ->validator('Validate::dateFormat', array(
            'format' => 'Ymd'
        ) )
        ->getFormatter('Format::datetime', array(
            'from' => 'Ymd',
            'to' =>   'm/d/Y'
        ) )
        
        ->setFormatter('Format::datetime',array(
            'from' => 'm/d/Y',
            'to' =>  'Ymd'
        ) ),

I am out of ideas.

allan

The validator runs before the formatter. Use m/d/Y for the format property in the validator and it should run okay.

Allan

bbrindza

That worked Allan. Thank you for the second set of eyes.

gitamrajeev

hi all
does this formatting works to customize the cell format from General to Date or Currency in the excel sheet