Cannot get DateTime formatting to show as expected in editor

Cannot get DateTime formatting to show as expected in editor

KanthKanth Posts: 20Questions: 9Answers: 0
edited May 2020 in Editor

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?

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited May 2020

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

    Colin

  • KanthKanth Posts: 20Questions: 9Answers: 0

    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.

  • KanthKanth Posts: 20Questions: 9Answers: 0
    edited May 2020

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

This discussion has been closed.