Invalid date format

ralph.ryl

How is it possible, to insert an empty value (or NULL value) with the editor (1.3.3) for a database column with date format?
I use jQueryUI datepicker and if i leave the field blank there comes an error "SQLSTATE[22007]: Invalid datetime format:»«". The database field can be empty and in php-file the date field has no indication for validation.

Thanks for your help

Ralph

allan

Hi Ralph,

What is the data that is being submitted to the server (you can check using the developer tools in your browser to see the Ajax request)?

With the PHP libraries you can use the nullEmpty() formatter to convert an empty string to null for the database (since HTTP variables can't be null - they are just strings.

Field::inst( 'update' )->getFormatter( 'Format::nullEmpty' );

Allan

ralph.ryl

Hi Allan,
the submitting data for the date-field is empty

data[end_date]  2014-11-07
data[f1]    0
data[fo]    0
data[g1]    0
data[gr]    0
data[gv]    0
data[id]    0
data[institution]   j
data[internal]  0
data[la]    0
data[lf]    0
data[mm]    0
data[name]  h
data[oz]    0
data[ph]    0
data[s1]    0
data[so]    0
data[ss]    0
data[start_date]    
data[title] u
id  row_0

OR

action=edit&data%5Bid%5D=0&data%5Btitle%5D=u&data%5Bname%5D=h&data%5Bemail%5D=b&data%5Binstitution%5D=j&data%5Binternal%5D=0&data%5Bstart_date%5D= &data%5Bend_date%5D=2014-11-07&data%5Bc1%5D=0&data%5Bcc%5D=0&data%5Bs1%5D=0&data%5Bso%5D=0&data%5Bss%5D=0&data%5Bf1%5D=0&data%5Bfo%5D=0&data%5Bg1%5D=0&data%5Bgr%5D=0&data%5Bdp%5D=0&data%5Bla%5D=0&data%5Blf%5D=0&data%5Bmm%5D=0&data%5Bgv%5D=0&data%5Bph%5D=0&data%5Baq%5D=0&data%5Boz%5D=0&id=row_0

see data[start_date]
So i think the getFormatter-call didn't help at this position.

allan

Hi,

Did you try the getFormatter? It should translate an empty string into a null for the database. It sounds like the database is currently rejecting empty (zero length) strings for that column and a null might be the value that it will accept.

Allan

ralph.ryl

Yes, i think so, table.refs.php looks like

...
Field::inst( 'start_date' )->getFormatter( 'Format::nullEmpty' ),
...

allan

Doh - sorry. I should have said setFormatter()! The getFormatter() is for getting the data, setFormatter() for setting it!

Allan

ralph.ryl

Perfect, thanks for your help

ralph.ryl

Hi Allan,
the solution is ok for viewing dates in my format (->getFormatter( 'Format::date_sql_to_format', 'm/Y' )). But if i edit the values with datatables editor, date (with and without datepicker) get wrong (add 6 month and 6 years). I try to set ->setFormatter( 'Format::date_format_to_sql', 'yyyy-mm-dd' ) OR ->setFormatter( 'Format::Format::DATE_ISO_8601' ) with no success. Maybe you can help me again.
Thanks
Ralph

allan

Are you editing using the m/Y format? Do you also have the set formatter using that format?

Allan

ralph.ryl

No, I just want to view the date in datatable as 'm/Y', for editor I the using is ISO8601.
With setting the getFormatter the date in editor shows wrong, by try to set the setFormatter like i worte above, nothing changed. I try to change the order of set-/getFormatters with no success.

ralph.ryl

I just want to have two date format for the same column. In the view I want 'm/Y' and when editing the values there I want to select the complete date for saving in database (ISO 8601).

Thanks
Ralph

allan

I see - you want the date in two different formats. So what I would suggest for that is that you have two different Field's which describe what you want - only one of them would be used to set the value though:

        Field::inst( 'updated_date' )
            ->validator( 'Validate::dateFormat', array(
                    "empty" => false,
                    "format" => Format::DATE_ISO_8601,
                    "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
        Field::inst( 'updated_date', 'table_update_date' )
            ->set( false )
            ->getFormatter( 'Format::date_sql_to_format', 'm/Y' ),

Note the use of the second parameter for the Field creation - that is the name that the value is written to in the JSON. So use columns.data to access it: data: 'table_update_date'.

Note also the set( false ) to ensure that it isn't written to at all.

Allan

ralph.ryl

Hi Allan,
your suggestion didn't help me. The validator part is ok but it wont solve the problem. The date shows still wrong.
The code below is my original code, where the view of table is in format m/Y (or any other without day) and in the editorview the date always show an wrong future date. If i comment out the getFormatter everything works good.

Field::inst( 'start_date' )

        ->setFormatter( 'Format::nullEmpty' )

        ->getFormatter( 'Format::date_sql_to_format', 'm/Y' )

        ->setFormatter( 'Format::Format::DATE_ISO_8601' ),

Maybe i have an error in reasoning and it is simple but my ideas run out.

Ralph

allan

Let's step back for a moment. You want:

• In the table: m/Y
• In Editor and the database: ISO8601 (i.e Y/m/d).

Is that correct?

If so, did you try my suggestion above of reading the field from the database using two different Field instances? If you want the date in two different formats, that is how I would suggest you do it.

Allan

ralph.ryl

Correct, this is my request.
I try your suggestion. The code i try was:

Field::inst( 'start_date' )
  ->validator( 'Validate::dateFormat', array(
    "empty" => true,
    "format" => Format::DATE_ISO_8601,
    "message" => "Please enter a date in the format yyyy-mm-dd"
))
->setFormatter( 'Format::nullEmpty' )
->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),

Field::inst( 'start_date', 'start_date' )
  ->set( false )
  ->getFormatter( 'Format::date_sql_to_format', 'm/Y' ),

Field::inst( 'end_date' )->setFormatter( 'Format::nullEmpty' )
  ->getFormatter( 'Format::date_sql_to_format', 'M Y' ),

Maybe I missunderstand you, please correct me if when my code is wrong.
As you can see, I have two date-fields. The database-fieldnames are start_date and end_date.

Thanks

Ralph

allan

Field::inst( 'start_date', 'start_date' )

Aliasing it to itself isn't going to do any good! If you have a look at my code above, I selected a different name, otherwise you have a name conflict.

Allan

ralph.ryl

Yes Allan, you are right. I understand you now.
For others with the same problem here the solution:

1. file: table.TABLE.js
   in datatable-code ($('#refs').dataTable) rename the date-column(s) to *_view

2. file: table.TABLE.php
   make two field::inst per date-column, first the one for edit
   in my example it is:

Field::inst( 'start_date' )
    ->validator( 'Validate::dateFormat', array(
     "empty" => true,
    "format" => Format::DATE_ISO_8601,
     "message" => "Please enter a date in the format yyyy-mm-dd"
     ) )
     ->setFormatter( 'Format::nullEmpty' )
     ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
     ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),

and a second for the view, in my example it look

Field::inst( 'start_date', 'start_date_view' )
     ->set( false )
    ->getFormatter( 'Format::date_sql_to_format', 'm/Y' ),

here ist the second parameter (start_date_view) the same name like in table.refs.js, it is in the sql-query for:

SELECT ... start_date, start_date AS start_date_view, ... FROM TABLE ...

Now you see the *_view-column in datatable and the original column in editor.

Thanks for your help Allan

allan

Good to hear we got it working the way you want!

Allan