Date formatting using inline editing with date picker.

Date formatting using inline editing with date picker.

bbrindzabbrindza Posts: 298Questions: 68Answers: 1

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>

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

    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',
                        }, 
    } );
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

    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'
            ) ),
            
    
  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

    Got it. Thanks for the update.

  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

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

    Allan

  • bbrindzabbrindza Posts: 298Questions: 68Answers: 1

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

  • gitamrajeevgitamrajeev Posts: 3Questions: 0Answers: 0

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

This discussion has been closed.