Editor: Accept any Date Formats | Accept any datatime formats Date Formatter Inline Editor

aungkohein

Hi guys!

This is probably an overkill but my user would like to input date format in Inline Editor using any of these: 21-01-2019, 21-01-19, 21-01, 21-1-2019, 21-1-19, 21-1, / US date formats Etc, auto-convert and register into the database.

Is this possible?

Much thanks!!

rf1234

sure, you just send the data to the server for validation and use a setFormatter to make sure the right values are sent to the database.

If you use European and US date formats in parallel you need to make sure you can always disambiguate the date which obviously you won't be able to if you don't impose certain rules to the user. E.g. 02/01/18 can mean Feb, 1st, 1918. Jan, 2nd 1918, Jan 2nd 2018 etc. etc.You have absolutely no way to tell from this user entry.

PHP uses this rule to disambiguate dates. I copied this from the docs a while ago:
"Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components:
if the separator is a slash (/), then the American m/d/y is assumed;
whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.
If, however, the year is given in a two digit format and the separator is a dash (-), the date string is parsed as y-m-d."

This is part of my setFormatter Code for dates. I only allow English (UK) and German date entries. US date entries aren't permitted. As you can see I make sure that nothing will be disambiguated as American because I eliminate the '/' before I convert to the database format.

   $val = str_replace(
        ['Januar ', 'Februar ', 'März ', 'April ', 'Mai ', 'Juni ', 'Juli ',
            'August ', 'September ', 'Oktober ', 'November ', 'Dezember '],
        ['January ', 'February ', 'March ', 'April ', 'May ', 'June ', 'July ',
            'August ', 'September ', 'October ', 'November ', 'December '],
        $val);
    $val = str_replace('/', '-', $val);
    $val = str_replace('.', '-', $val);
    
    if ($val == '00-00-0000' || $val == '0000-00-00 00:00:00') {
        return null;
    }
    
    $dateTime = new DateTime($val);    
    return $dateTime->format('Y-m-d H:i:s');

For validation you could use something like this. You put the code above into a function, e.g. "setFormatter" and if it returns "1970-01-01 01:00:00" you know that no valid date was entered. I have something like this in my validation code:

if ( isset($data['expired']) ) {
    if ( $data['expired'] > '0'  &&
         ( setFormatterDate($val) === '1970-01-01 01:00:00' ||
           $val <= '' )                                       ) {  //date invalid
        if ($_SESSION['lang'] === 'de') {   
            return 'Bitte geben Sie das Datum des Vertragsendes ein!';
        } else {
            return 'Please enter the contract expiration date!';
        }                
    }
}

aungkohein

Hi @rf1234!

Thank you for the help and very much appreciated your reply! Here is my syntax. I'm not sure if I translate your message right; I'm having ajax error -

Example 1: Ajax error. For more information about this error, please see http://datatables.net/tn/7

Field::inst( 'startDate' )
->validator(

            if ( isset($data['expired']) ) {
            if ( $data['expired'] > '0'  &&
                 ( setFormatterDate($val) === '1970-01-01 01:00:00' ||
                   $val <= '' )                                       ) {  //date invalid
                if ($_SESSION['lang'] === 'de') {  
                    return 'Bitte geben Sie das Datum des Vertragsendes ein!';
                } else {
                    return 'Please enter the contract expiration date!';
                }               
                }
            }
        ) )
        ->getFormatter( Format::dateSqlToFormat( 'd/m/y' ) )
        ->setFormatter( 

             $val = str_replace('/', '-', $val);
             $val = str_replace('.', '-', $val);

             if ($val == '00-00-0000' || $val == '0000-00-00 00:00:00') {
                 return null;
             }

             $dateTime = new DateTime($val);   
             return $dateTime->format('Y-m-d H:i:s');

        ),

Example 2: Ajax error. For more information about this error, please see http://datatables.net/tn/7

    Field::inst( 'startDate' )
        ->validator( 'Validate::dateFormat', array(
            'format' => 'd/m/y'
        ) )
        ->getFormatter( Format::dateSqlToFormat( 'd/m/y' ) )
        ->setFormatter( 

             $val = str_replace('/', '-', $val);
             $val = str_replace('.', '-', $val);

             if ($val == '00-00-0000' || $val == '0000-00-00 00:00:00') {
                 return null;
             }

             $dateTime = new DateTime($val);   
             return $dateTime->format('Y-m-d H:i:s');


            if ( isset($data['expired']) ) {
            if ( $data['expired'] > '0'  &&
                 ( setFormatterDate($val) === '1970-01-01 01:00:00' ||
                   $val <= '' )                                       ) {  //date invalid
                if ($_SESSION['lang'] === 'de') {  
                    return 'Bitte geben Sie das Datum des Vertragsendes ein!';
                } else {
                    return 'Please enter the contract expiration date!';
                }               
                }
            }
        ),

My Js Code:

{
"label": "Start Date",
"name": "start_date",
"type": "datetime",
"format": "DD\/MM\/YY"
},

aungkohein

Hi @rf1234,

I found this. Not sure if can add IF condition to these...

    Field::inst( 'startDate' )
        ->validator( 'Validate::dateFormat', array(
            'format' => 'd/m/y' // this should be equal to DD\/MM\/YY in js ya?
        ) )
        ->getFormatter( 'Format::datetime',array(
       'from' => 'Y-m-d',  // from db to table
       'to' =>  'd/m/y' ) )
        ->setFormatter( 'Format::datetime',array(
       'from' => 'd/m/y', // from table to db
       'to' =>  'Y-m-d' ) ), 

@colin

rf1234

Hi your syntax doesn't look good actually. You need to pass variables into your setFormatter functions.

Maybe you check this first. Look at "Custom Formatters"
https://editor.datatables.net/manual/php/formatters

Here are some sample field instances (PHP) with the respective custom validator, getFormatter and setFormatter calls.

Field::inst( 'contract.expired' ),
Field::inst( 'contract.exp_date' )
        ->validator( function ( $val, $data, $opts ) {
            return validatorExpDate($data['contract'], $val);
        } )
        ->getFormatter( function ( $val, $data, $opts ) {
            return getFormatterDate($val);

        } )
        ->setFormatter( function ( $val, $data, $opts ) {
            if ($data['contract']['expired']  > '0') {
                return setFormatterDate($val);
            } else {
                return null;
            }
        } ),

and a sample validator function (your's will certainly look much different ...):

function validatorExpDate (&$data, &$val) {
    if ( isset($data['comments'])           ||
         isset($data['comments_creditor'])  ||
         isset($data['comments_govdept'])   ||
         isset($data['communication'])      ||
         isset($data['description'])   ) {
        return true;
    } else {
        if ( isset($data['expired']) ) {
            if ( $data['expired'] > '0'  &&
                 ( setFormatterDate($val) === '1970-01-01 01:00:00' ||
                   $val <= '' )                                       ) {  //date invalid
                if ($_SESSION['lang'] === 'de') {   
                    return 'Bitte geben Sie das Datum des Vertragsendes ein!';
                } else {
                    return 'Please enter the contract expiration date!';
                }                
            }
        }
    }        
    return true;
}

and a sample getFormatterDate

function getFormatterDate(&$val, $format = null, $lang = null) {
    if ($val == null) {
        return '';
    } else {
        $date = new DateTime($val);
    }
    //if language doesn't get passed and we are in batch: we use German format
    if ( is_null($lang) ) {
        if ( isset($_SESSION['lang']) ) {
            $lang = $_SESSION['lang'];
        } else {
            $lang = 'de';
        }
    }
    if ( is_null($format) ) {
        if ($lang === 'de') {  
            return $date->format("d.m.Y");
        } else {
            return $date->format("d/m/Y");
        }
    } else { //other formats Ymd and dmy
        if ( $format === 'Ymd' ) {
            return $date->format("Ymd");
        } elseif ( $format === 'dmy') {
            if ($lang === 'de') {  
                return $date->format("d.m.y");
            } else {
                return $date->format("d/m/y");
            }
        } elseif ( $format === 'dm') {
            if ($lang === 'de') {  
                return $date->format("d.m.");
            } else {
                return $date->format("d/m/");
            }
        }
    }
    return $val;
}

and sample setFormatter (really poorly coded don't copy 1:1)

function setFormatterDate($val = null) {  
    //Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: 
    //if the separator is a slash (/), then the American m/d/y is assumed; 
    //whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. 
    //If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.
    if ( is_null($val) ) {
        return $val;
    }
    if ( $val <= '' ) {
        return null;
    }
    $val = str_replace(
        ['Januar ', 'Februar ', 'März ', 'April ', 'Mai ', 'Juni ', 'Juli ',
            'August ', 'September ', 'Oktober ', 'November ', 'Dezember '],
        ['January ', 'February ', 'March ', 'April ', 'May ', 'June ', 'July ',
            'August ', 'September ', 'October ', 'November ', 'December '],
        $val);
    $val = str_replace('/', '-', $val);
    $val = str_replace('.', '-', $val);
    
    if ($val == '00-00-0000' || $val == '0000-00-00 00:00:00') {
        return null;
    }
    
    $dateTime = new DateTime($val);    
    return $dateTime->format('Y-m-d H:i:s');
}