SqlServer/.NET DateTime in input field

wadeparallon

Description of problem:

I have various issues that just aren't affecting my implementation or I'm doing it wrong. These all are probably related in some way.

• Despite the messaging here: https://editor.datatables.net/reference/field/datetime about changing the displayFormat on the value in the INPUT field, Its showing as a datetime still. I get no error message in console about moment.js. Datatable view formats just fine

Editor js

            {
                label: 'StartDate',
                name: 'StartDate',
                type: 'datetime',
                def: () => new Date(),
                displayFormat: 'MM-DD-yyyy'
            },
            {
                label: 'EndDate',
                name: 'EndDate',
                type: 'datetime',
                def: () => new Date(),
                displayFormat: 'MM-DD-yyyy',
                
            }

Datatable js

            {
                data: "StartDate",
                title: "StartDate",
                render: function(data, type, row) {
                    return moment(data).format("MM-DD-yyyy");
                }
            },
            {
                data: "EndDate",
                title: "EndDate",
                render: function(data, type, row) {
                    return moment(data).format("MM-DD-yyyy");
                }
            }

• Editor in .net seems to have zero affect using the GetFormatter. I've tried many different variations based on examples, but its untouched. I know I have two different ISO's in screenshot, I've been trying to get at least something to show changed in the INPUT field. https://editor.datatables.net/manual/net/formatters#Date-and-time
  

• DatePicker isn't defaulting its date on selection.
  

• Datatables is string sorting, not date sorting

allan

Can you show me an example of the JSON data that is being loaded for the rows? Your GetFormatter looks like it should result in the data being in YYYY-MM-DD format, which is ideal.

Datatables is string sorting, not date sorting

Use the datetime renderer - at the moment you are returning a string for all types - hence why it is string sorting. The renderer will handle the formatting for you:

render: DataTable.render.datetime('MM-DD-YYYY')

Example here.

displayFormat

Could you try:

            displayFormat: 'MM-DD-YYYY',
            wireFormat: 'YYYY-MM-DD',

please?

It will depend on what the server is returning, but hopefully that will help. If not, it would be really useful if you could link to a page showing the issue so I can help to debug it and see the relevant data.

Allan

wadeparallon

Server is returning this:

{"draw":null,"data":[{"DT_RowId":"row_1","Id":1,"StartDate":"2018-06-01T00:00:00","EndDate":"2018-09-01T00:00:00" ....

Which it shows in the input field.

I apologize but right now the page only exists in local development. There is nothing special about the date being returned. Its a SQL Server Datetime, to a C# DateTime property.

wadeparallon

I tried the wireFormat before, but might have not tried it with the current puzzle pieces in place.

Let me double check on that property.

wadeparallon

From your comments it looks like the .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601 )) isn't returning what its supposed to.

{
                data: "StartDate",
                title: "StartDate",
                render: function(data, type, row) {
                    return DataTable.render.datetime('YYYY-MM-DD');
                }
            },

Makes the Datatables js show blank.

{
                label: 'EndDate',
                name: 'EndDate',
                type: 'datetime',
                def: () => new Date(),
                displayFormat: 'MM-DD-yyyy',
                wireFormat: 'YYYY-MM-DD'
                
            }

Makes the Editor input field show blank.

wadeparallon

Just like to add that I have been looking at the DatesController and the DateTimeController in the examples in .NET package and I feel like I've done the same thing as those controllers.

I'll continue to examine.. but as of right now I'm still at a loss.

wadeparallon

I've replicated the code exactly whats going on in datescontroller but no luck.

Here are my includes:

allan

As you say, the response from the server-side doesn't look right. The DATE_ISO_8601 token is the correct one to use. I'm not sure why that isn't working!

What version of the dll are you using? Also, what is the data type of the column in the database?

Allan

wadeparallon

@allan thank you for trying to figure this out. Here is where I'm sitting now. I can successfully change the date format from the Datatable JS in the datatable, but I can never seem to touch the format of the date in the input (contrary to the example I'm mimicking /examples/dates/dates.html)

Editor DLL is 2.3.2.0

Database (StartDate/EndDate Columns)

Code (modeled off the datescontroller example)

Data returned:

Datatable JS (modeled off of dates.html example)

$(document).ready(function () {
    const tableData = document.querySelector("#PlanningPeriodsDatatable")
    
    const editor = new DataTable.Editor({
        ajax: '/Organizations/'+tableData.dataset.organizationid+'/PlanningPeriods/Editor',
        idSrc:  'Id',
        formOptions: {
            main: {
                focus: 'none'
            }
        },
        fields: [
            {
                label: 'StartDate',
                name: 'StartDate',
                type: 'datetime',
                def: function () {
                    return new Date();
                }
            },
            {
                label: 'EndDate',
                name: 'EndDate',
                type: 'datetime',
                def: function () {
                    return new Date();
                }
            }
        ],
        table: '#PlanningPeriodsDatatable'
    });

    const datatable = new DataTable("#PlanningPeriodsDatatable", {
        ajax: {
            url: "/Organizations/"+tableData.dataset.organizationid+"/PlanningPeriods/Editor",
            type: "GET",
            dataType: "JSON"
        },
        order: [2, "asc"],
        columns: [
            {
                data: null,
                orderable: false,
                searchable: false,
                render: DataTable.render.select(),
                width: '40px'
            },
            {
                data: "Id",
                title: "Id",
                visible: false,
                searchable: false
            },
            {
                data: "StartDate",
                title: "StartDate"
            },
            {
                data: "EndDate",
                title: "EndDate"
            }
        ],
        "initComplete": function () {
            $(this).css("opacity", 1);
        },
        layout: {
            topStart: {
                buttons: [
                    { extend: 'create', editor: editor },
                    { extend: 'edit', editor: editor }
                ]
            }
        },
        select: {
            style: 'single',
            selector: 'td:first-child'
        }
    });
});

C# Model:

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

Output

Side note: I noticed that the column is LEFT aligned when its reading it as a string, but when its reading it as a datetime, everything is RIGHT aligned. Is this intended?

wadeparallon

Hmm... just noticed my datetime column... there is a slight difference:

 datetime2(7)  2024-06-01 00:00:00.0000000
 datetime      2011-12-31 00:00:00.000

allan

Hmmm - I'll set up a test database here with a datetime2(7) - I'm wondering if that is the issue. My tests use datetime and I wonder if the formatter isn't quite coping with the different time (although I would have thought it would map to a DateTime and this suggests that it does).

Sorry I don't have an immediate answer - there will be a solution though! As I say, I'll try a test with datetime2(7) here.

Allan

wadeparallon

Hey no problem. I think we've narrowed down the issue.

Just an FYI, this is the default datatype in sql server being set by Entity Framework in code first these days. At least thats what its looking like.

So if we can improve going forward or at least figure out how to handle it in that format.. then progress is progress. And I'll be happy to have helped.

allan

On SQL Server 2022 I've just tried my demos with datetime2(7) and they appear to operate as expected. and the code follows the path I'd expect.

What happens if you do something like:

.GetFormatter(Format.DateSqlToFormat("dddd d MMMM yyyy))

Does it still come through in the full ISO8601 format?

Allan

wadeparallon

Like this? Looks like zero impact.

allan

That's the darndest thing. What happens if you use:

.GetFormatter((val, data) => "Test")

?

At the moment it is like the formatter is being complete ignored. Can you send me the full controller?

Allan

wadeparallon

I'll PM you the full controller. Looks like its casting it back to DateTime (based on Model property) Might be translating it then retranslating it back to DateTime.

allan

based on Model property

Ah!!! Can you change it to a string please? That might well solve the issue outright.

Per the docs for the Modals:

In practice this means that string, int and Decimal are the data types that should be used. Other data forms such as DateTime fields should be given as a string, which Editor's libraries will convert automatically.

Allan

wadeparallon

I can try, but just a reminder...

allan

This is what the current demo uses:

namespace EditorNetCoreDemo.Models
{
    public class DatesModel
    {
        public string first_name { get; set; }

        public string last_name { get; set; }

        public string updated_date { get; set; }

        public string registered_date { get; set; }
    }
}

Possibly you've got an old version of the demo?

Allan

wadeparallon

My demo was downloaded on 11/6/2024 and is labeled as Editor-NETCore-2.3.2.

But the demo works, at least in the input.

Strange.

I'd like to add, that at one point I had everything working, except for the input form field and the default date being selected when the datepicker opens - which is the opposite of the demo.

wadeparallon

I do apologize... I must have modified that in testing and inadvertently forgot about that. That is the only explanation.

So I'm guessing what is happening is

1. Editor pulls data from SQL
2. GetFormatter put it into a string format I wanted
3. And then C# bound that back to a DateTime, which put it BACK to the wrong format
4. In JS I was taking that and rendering it into the string format I wanted, so I thought it was working
5. Input is pulled directly from data, or results of number 3, so it shows wrong

My issue now then is I'm modifying the entity model, and changing that to a string requires DB changes.

So there is no way to modify a DateTime from SQL via Editor? That doesn't seem right.

Can we have the editor show a different field but modify a different property? I'd have to manually handle in precreate/predit I'm assuming.

Set string property via editor, get pulls different column and makes it a string.
on Save, translate back into datetime column.. something like that? oof.

rf1234

So there is no way to modify a DateTime from SQL via Editor? That doesn't seem right.

Well you can either return the display format from the server or you can easily use moment.js and other plug ins to do the formatting for you client side (or probably even built-in Editor functionality).

I am mostly using get and set formatters on the server to do the conversion. But either way it works perfectly fine. I have no idea why you are thinking this wasn't possible

If your "allegation" was correct, I certainly would have never started using Editor - and I started using it in 2016.

allan

So there is no way to modify a DateTime from SQL via Editor? That doesn't seem right.

You can certainly have a DateTime in the SQL - that's no issue at all. However, the modal does not currently support DateTime variables. Indeed, given that you want to use a plain text format to represent the data (via the formatter) then it has to be a string. That might mean that you have to have two modals for the same data, one for EF, and one for Editor.

I realise that isn't ideal, but currently that's how it would need to be if you need a DateTime property. What I should perhaps look into is how to support the unformatted DateTime string on the client-side.

Allan

wadeparallon

Sorry, I meant "to display within an input". I know I modify/render out to datatables however I want, been doing that for years as well.

I guess it is the plugin issue, not Editor. I apologize I didn't mean to attack

I appreciate the help. I'll look into adjust via model

allan

It wasn't taken as such. It does appear that this is an area that Editor could be improve (there are many such areas as with all non-trivial software ).

Allan

wadeparallon

Thank you, I've finished up this section by just recreating my entity model with a view model and having the dates be strings.

I did a little more research on my side note above:

Side note: I noticed that the column is LEFT aligned when its reading it as a string, but when its reading it as a datetime, everything is RIGHT aligned. Is this intended?

I can't override it either. I can use className: 'dt-left' and it's added to the html element, but dt-right overrides it. I could more than likely use head and body options independently as a workaround, but just pointing this out.

I'm not sure why datetime has an alignment constraint that is different than default on all other columns in the first place though.

(I've given up on changing the format in the inputs. Even the demos have it blanked out if the format is different. 8601 it is!)

allan

Dates, times and numbers are right aligned by default. If you want to remove that you can do:

DataTable.type('date', 'className', '');

You might need to use something other than date for the first parameter - i.e. if you have used DataTable.dateTime() to create a new type, the new type name would be used there.

Allan