error message by DateTime picker

Hildeb67

Hello everyone. I'm using a DateTime picker in German format and would like to store the time and date in a mysql datetime field. I have following code.

 fields: [
                    {   
                        type: 'datetime',
                        label: "Einsatzbeginn:",
                        name: "ESBEGINN",   
                        format: 'DD/MM/YYYY HH:mm',
                        def: () => new Date(),
                        displayFormat: 'DD.MM.YYYY HH:mm'
                    }
    

Field::inst( 'ESBEGINN' )
            ->validator( Validate::dateFormat(
                'j M Y H:i'
            ) )
            ->getFormatter( Format::datetime(
                'Y-M-d H:i:s',
                'j M Y H:i'
            ) )
            ->setFormatter( Format::datetime(
                'j M Y H:i',
                'Y-m-d H:i:s'
            ) ),

Unfortunately I get the error message 'Input not valid'. What am I doing wrong?

Thanks for your help

allan

Hi,

Looks like there are a few things going on - first, don't use format and displayFormat together. Either use format alone, or use displayFormat and wireFormat.

You would use format if the server is sending you formatted data (it is in this case). You would use displayFormat and wireFormat if you were to have the server send ISO8601 data (which I'd prefer to see).

So option 1: Remove displayFormat and change format to make the format being sent from the server - in PHP you have 'j M Y H:i' - which in Moment tokens is: D MMM YYYY HH:mm. That's your fastest route to a fix.

Option 2: Remove the get / set formatters at the client-side and use client-side rendering for the dates. Use DataTable.render.date() for the column and displayFormat and wireFormat for Editor. If you have customers who might be using a different locale from you - e.g. American's, then you might want to consider this approach.

Allan

Hildeb67

Hi Allan,
thanks for the detailed solution but unfortunately it doesn't work for me. Do you have an example for me? Also for the php site? Chris

allan

This is the server-side formatted date example. The PHP script is available by clicking the "Server script" tab below the table.

If you are able to give me a link to your page I can take a look at what is going on.

Allan

Hildeb67

Hi Allan,
here is the link to the page
https://www.futuretecaugsburg.de/src/FOM/einsatz_neu.php

allan

The server appears to be responding with just the ISO8601 date part. Not the formatted date as might be expected from:

            ->getFormatter( Format::datetime(
                'Y-M-d H:i:s',
                'j M Y H:i'
            ) )

Can you show me your full PHP code as well please?

Allan

Hildeb67

<?php
// DataTables PHP library
include( "../lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
    
Editor::inst( $db, 'ffweinsatz' )// Tabelle aus Datenbank
    ->fields(
        Field::inst( 'ESBEGINN' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
        Field::inst( 'ESENDE' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),        
        Field::inst( 'ELID' )
            ->options( Options::inst()
                ->table( 'ffwmitglieder' )
                ->value( 'ID' )
                ->label('NACHNAME') 
                            
            ->where( function ($q) {
                        $q->where( 'STATUS', '%1%', 'LIKE' );                   
                    })              
                ->label( array('NACHNAME', 'VORNAME') )
                ->render( function ( $row ) {
                    return $row['NACHNAME'].' '.$row['VORNAME'].'' ;
                    } )         
            ),      
        Field::inst( 'ELALTERNATIV' ),
        Field::inst( 'OEZU' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'ESTRASSE' ),
        Field::inst( 'EHSNR' ), 
        Field::inst( 'EPLZ' ),
        Field::inst( 'EORT' ),  
        Field::inst( 'ESSTELLE' ),  
        Field::inst( 'KLEINBRAND' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,           
        Field::inst( 'MITTELBRAND' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,           
        Field::inst( 'GROSSBRAND' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'BGELOESCHT' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'WOHNUNG' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,           
        Field::inst( 'VERWALTUNG' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'WARENHAUS' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'HANDWERK' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'HOTEL' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'VERSAMMLUNG' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'HEIM' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'GARAGE' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'BAU' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'LAND' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'FZG' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'INDU' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'FREI' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'MUELL' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'SONSTTEXT' ),         
        Field::inst( 'WOHNUNGBA' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'GEBAUEDE' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'NGEBAUEDE' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'GSTOFFE' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'RSTOFFE' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) ,
        Field::inst( 'EINSPEKTION' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ) 
        
    )
    ->debug(true)
    ->process( $_POST )
    ->json();

Hildeb67

is that sufficient?

Hildeb67

Hello Allan, is this the information you need?

allan

Hi,

Apologies for having not been able to reply over the weekend.

I don't actually see:

->getFormatter( Format::datetime(
    'Y-M-d H:i:s',
    'j M Y H:i'
) )

in your code at all?

Instead it has:

        Field::inst( 'ESBEGINN' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),

Which results in an ISO8601 date part, which is what is shown in the JSON response screenshot you showed above.

So at the moment, the code is doing what is expected.

What I'm not clear on is where the j M Y H:i comes into play? I'd have expected it to be on that field, but it isn't?

Allan

Hildeb67

Thanks Allan, I've now solved it like this:

fields: [
                    {   
                        type: 'datetime',
                        label: "Einsatzbeginn:",
                        name: "ESBEGINN",
                        def: () => new Date(),                      
                        format: 'D.M.YYYY HH:mm'        
                    },

Field::inst( 'ESBEGINN' )       
            ->validator( 'Validate::dateFormat', array(
                "format"  => "d.m.Y H:i",
                "message" => "Falsches Format"
                ) )     

            ->setFormatter( function ( $val, $data, $opts ) {
                if ($val <= '' || $val == null) {
                return null;
                }
                $val = str_replace('/', '-', $val);
                $dateTime = new DateTime($val);   
                return $dateTime->format('Y-m-d H:i:s');
                } ),

Many greetings, Chris

allan

Hi Chris,

Awesome - good to hear you've got it working now.

Allan