Cannot get DateTime formatting to show as expected in editor

Kanth

I'm using the inline editor for a datatime field. I am having no end of problems getting it to work properly.
The SQL DB is returning JSONized data like "2020-05-02T10:00:09". It's fine if I add "render" to the datatable initializer

{ data: 'Orders.OrderDate', className: "text-center", "render": function (data, type, full) { return data == null ? "" : moment(data).format("M/D/YYYY h:mm A"); } },

and I get it to display as desired. However, when it goes into edit mode, I either get Invalid Date or it changes it back to the string format above.

In my server WebAPI2 I pull the response with

                Dim response = New Editor(db, "Orders", "OrderID").Model(Of OrderDTO)() _
                    .LeftJoin("OrderStatuses", "OrderStatuses.OrderStatusID", "=", "Orders.OrderStatusID") _
                    .Process(request).Data()

                '.Field(New Field("Orders.OrderDate") _
                '   .GetFormatter(Format.DateSqlToFormat("d/m/Y H:i")) _
                '   .SetFormatter(Format.DateTime("MM-dd-yyyy h:mm tt", "yyyy-MM-dd hh:mm"))) _

I've tried playing around with the validator and formatters, but I cannot get it to work.

Note the date in the pickers does not match the date string in the text box. It uses the current date. I assume this is because it doesn't understand the string format

So, 1) How do I get the dates to show correctly in the table and in the editor?
2) How do I get the pickers to show the correct values?

rf1234

I use this all the time but do the rendering server side. Depending on the user language I send 28.05.2020 or 28/05/2020 from the server. To achieve this I use Editor with a getFormatter to convert 2020-05-28 00:00:00 into the user formats and a setFormatter to convert the user formats back into the database format.

colin

You can use wireFormat and displayFormat for that too from the datetime - see example here.

Colin

Kanth

Thank you for your comments. I've tried both, but I'm apparently missing something. Server side, I tried using the formatters (see lines 4-6 above), but couldn't find the right magic to get it to work.
Client side, I tried various forms with:

displayFormat: 'MM-DD-YYYY h:mm A',
wireFormat: 'YYYY-MM-DD hh:mm:ss'

but couldn't get that to work either. Using the examples you graciously provided, I get "Invalid Date" in the editor.
It may be just the way I am setting the formats, but I can't figure out the right way to do it.

Kanth

OK, another weird behavior: using the formats @colin suggested, some rows work while other give invalid date. Even if they have the same date with different times, some lines work while others have invalid date. AM/PM doesn't matter.
"5/10/2020 12:08 PM" works while "5/10/2020 11:43 PM" gives invalid date.

allan

The wireFormat in this case should be wireFormat: 'YYYY-MM-DDThh:mm:ss' (since you have proper ISO8601 string there).

Regarding the invalid dates in the table, could you give me a link to your page please?

Thanks,
Allan