DATE PRE 1970

alord84

I am having a major issue with editing a date to any pre 1970 date.

tangerine

Please supply details of your "major issue".

allan

Ideally if you could link to the page you are having problems with, that would be great.

Given the point at which you are having problems, it sounds like a unix time stamp issue, so something somewhere is converting to / from a unix time stamp, and not being able to use negative numbers.

Regards,
Allan

alord84

Consider editing the Registered Date here -> and changing the date to any pre 1970 date.

https://editor.datatables.net/examples/simple/dates.html

I receive this Error - Date is not in the expected format

Any work around suggestions appreciated.

allan

Hi,

Thanks for pointing that out. The issue there is that in the database that field (start_date) is a timestamp field about which the MySQL manual says:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

A more appropriate field type would be DATE or DATETIME which have much wider allowed values.

I'll update my demo database with the next release.

Regards,
Allan

alord84

I have the database field type as Date, and still receive teh same error.
I also manually edited teh date in teh database to 1969-01-01 and load the page and it gives me the correct date. However, on edit to another pre 1970 date, I receive this Error - Date is not in the expected format

This might be an issue with the script? Please help

allan

If I update my database using:

ALTER TABLE `datatables_demo` CHANGE `start_date` `start_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Then I'm able to successfully edit the date column to be <1970.

However, on edit to another pre 1970 date, I receive this Error - Date is not in the expected format

Do you have a validator? If so, what is that validator?

Allan

alord84

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        "ajax": "../php/dates.php",
        "table": "#example",
        "fields": [ {
                label: "First name:",
                name:  "first_name"
            }, {
                label: "Last name:",
                name:  "last_name"
            }, {
                label:      "Updated date:",
                name:       "updated_date",
                type:       "date",
                def:        function () { return new Date(); },
                dateFormat: $.datepicker.ISO_8601
            }, {
                label:      "Registered date:",
                name:       "registered_date",
                type:       "date",
                def:        function () { return new Date(); },
                dateFormat: 'D, d M y'
            }
        ]
    } );


Editor::inst( $db, 'users' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        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( 'registered_date' )
            ->validator( 'Validate::dateFormat', 'D, j M y' )
            ->getFormatter( 'Format::date_sql_to_format', 'D, j M y' )
            ->setFormatter( 'Format::date_format_to_sql', 'D, j M y' )
    )
    ->process( $_POST )
    ->json();

This example is at the link
https://editor.datatables.net/examples/simple/dates.html

The dates are Update date and Registered date with date picker.

database Field Type is datetime

allan

Is it the registered_date field that you are having a problem with specifically?

It would appear that PHP's date() method does not like using the two digit year format for <1970. If you use the four digit format (Y rather than y in PHP and yy for jQuery UI) that should work as expected.

Allan

alord84

Perfect that works for JQuery UI - thanks