Editor vs. DataTables, date format and sorting by the date

Editor vs. DataTables, date format and sorting by the date

rldean1rldean1 Posts: 141Questions: 66Answers: 1

I have a dilemma. (this expand on a question I asked earlier.)

This is about keeping the date format consistent between Editor and DataTables.

If I send the date pre-formatted from SQL to DataTables, then how can I maintain the ability to sort by Date? Like, If I pre-format the date in SQL, then there is almost no need for the render sort/format plugins??? BUT, if I stop using that plug-in, then I can't sort by date!!!

AND, if I use render: $.fn.dataTable.render.moment("MM-DD-YYYY"), against the appropriately-formatted date, I get "Invalid Date" displayed in the table.

SQL:
select JobFairDate

DataTables columns render:
render: $.fn.dataTable.render.moment("YYYY-MM-DDTHH:mm:ss", "MM-DD-YYYY")



SQL:
cast(JobFairDate as date) as JobFairDate

DataTables columns render:
render: $.fn.dataTable.render.moment("YYYY-MM-DD", "MM-DD-YYYY")



SQL (strictly pre-formatted):
convert (varchar(10), JobFairDate, 110) as JobFairDate

DataTables columns render [displays "invalid date" in table]:
render: $.fn.dataTable.render.moment("MM-DD-YYYY")

The real question:

This last example produces "invalid date" in the table. I'm sending a date formatted exactly the way I want it to DT. BUT, how do I properly use the render option here?

This question has an accepted answers - jump to answer

Answers

  • rldean1rldean1 Posts: 141Questions: 66Answers: 1

    OKAY!!! So I looked at the plug-in. Argument shifting.

    Just for s***-and-giggles, I tried this:

    render: $.fn.dataTable.render.moment("MM-DD-YYYY", "MM-DD-YYYY")

    That worked.

    Am I being lazy? I'm using render plug-in primarily for the sort option! It's quick and easy and allows for formatting.

    Maybe that could be a feature request: Make it so that the Editor field matches the display cell in DataTables?

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin
    Answer ✓

    This is an area that I've committed improvements for that will ship in Editor 1.9.1. There are new displayFormat and wireFormat options for datetime which allows the date displayed in the Editor form to be different for the format of the data (with 1.9.0 and earlier it always operates on the raw data).

    I've also included correct sorting for the formatted dates in the examples.

    We expect 1.9.1 to ship before the end of the month. Colin is currently giving it a Q&A kicking :).

    Allan

This discussion has been closed.