Datetime issues in Datagrid, SQL server with Razor Pages

[Marco]

Hi,

I have a datetimepicker edit field in DataGrid. When I update the record the time is adjusted.

The time in grid is showing other times then in sql database.

Grid shows: 10-02-2019 03:30

SQL shows: 2019-02-10 15:30:00

When I click edit the timepicker is showing 15:30

When I save the record without editing the time in SQL is changed to 2019-02-10 14:30:00

How to fix this?

Regards,

Marco

    <ejs-grid id="Grid" locale="nl" rowSelected="rowSelected" allowFiltering="true" allowSorting="true" allowPaging="true" load="onLoad">

        <e-grid-editsettings allowAdding="true" allowDeleting="true" allowEditing="true" showDeleteConfirmDialog="true"></e-grid-editsettings>

        <e-data-manager url="/Voyages/Index?handler=DataSource" insertUrl="/Voyages/Index?handler=Insert" updateUrl="/Voyages/Index?handler=Update" removeUrl="/Voyages/Index?handler=Delete" adaptor="UrlAdaptor"></e-data-manager>

        <e-grid-filterSettings type="Excel"></e-grid-filterSettings>

        <e-grid-pageSettings pageSize="15"></e-grid-pageSettings>

        <e-grid-columns>

            <e-grid-column field="VoyageId" headerText="Id" isPrimaryKey="true" validationRules="@(new { required = true, number = true })" visible="false"></e-grid-column>

            <e-grid-column field="Product" headerText="Product" validationRules="@(new { required=true})" width="100px"></e-grid-column>

            <e-grid-column field="StartDate" headerText="Start" editType="datetimepickeredit" textAlign="Right" width="140px"></e-grid-column>

        </e-grid-columns>

    </ejs-grid>

<script>

    function onLoad() {

        this.dataSource.dataSource.headers = [{ 'XSRF-TOKEN': $("input:hidden[name='__RequestVerificationToken']").val() }];

        this.columns[2].format = { type: 'date', format: 'dd/MM/yyyy hh:mm' };

    }

</script>

[Hariharan J V]

Hi Marco, 

We have validated the reported issue and found that timezoneoffset is cause of this issue. TimezoneOffset will affect the converted string, when we stringify the date object. So we suggest you to use the following solution in actionBegin event of the Grid to resolve this issue. Please refer the following code example for your reference. Please use this below solution instead of  solution provided in previous update. 

<ejs-grid id="Grid" allowPaging="true" actionBegin="begin" toolbar="@(new List<string>() {"Add", "Edit", "Update", "Delete" })"> 

... 

    <e-grid-columns> 

       ... 

   </e-grid-columns> 

</ejs-grid> 

 

<script> 

    function begin(args) { 

        if (args.requestType == "save") { 

            var grid = document.getElementById("Grid").ej2_instances[0]; 

            var cols = grid.columns; 

            for (var i = 0; i < cols.length; i++) { 

                if (cols[i].type == "date" || cols[i].type == "datetime") { 

                    var date = args.data[cols[i].field]; 

                    args.data[cols[i].field] = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate(), date.getHours(),date.getMinutes(), date.getSeconds(), date.getMilliseconds())); 

                } 

            } 

        } 

    } 

 

</script> 

Please get back to us if you need further assistance. 

Regards, 

Hariharan 

[Marco]

Hi,

That is almost working. In my grid I have 4 columns of type date. The javascript function shows in  console.log(cols); that all 4 date columns are type "date". 

In the for loop the 1st date columns is corrected the next 3 columns looses their type. The porperty cols[i].type returns Null.

<ejs-grid id="Grid" locale="nl" rowSelected="rowSelected" allowFiltering="true" allowSorting="true" allowPaging="true" load="onLoad">

        <e-grid-editsettings allowAdding="true" allowDeleting="true" allowEditing="true" showDeleteConfirmDialog="true"></e-grid-editsettings>

....

        <e-grid-columns>

....

            <e-grid-column field="StartDate" headerText="Start" editType="datetimepickeredit" textAlign="Right" width="140px"></e-grid-column>

            <e-grid-column field="DepartDate" headerText="Depart" editType="datetimepickeredit" textAlign="Right" width="140px"></e-grid-column>

            <e-grid-column field="ArrivalDate" headerText="Arrival" editType="datetimepickeredit" textAlign="Right" width="140px"></e-grid-column>

            <e-grid-column field="EndDate" headerText="End" editType="datetimepickeredit" textAlign="Right" width="140px"></e-grid-column>

        </e-grid-columns>

    </ejs-grid>

    function actionBegin(args)

    {

        if (args.requestType === "save") {

            args.data['ShipId'] = ddlShips.filter(function (d) { return d.ShipName === args.data['ShipName'] })[0].ShipId;

            var grid = document.getElementById("Grid").ej2_instances[0]; 

            var cols = grid.columns; 

            console.log(cols);

            for (var i = 0; i < cols.length; i++) { 

                console.log(cols[i].type);

                if (cols[i].type == "date" || cols[i].type == "datetime") { 

                    var date = args.data[cols[i].field]; 

                    //args.data[cols[i].field] = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate(), date.getHours(),date.getMinutes(), date.getSeconds(), date.getMilliseconds())); 

                    console.log(args.data[cols[i].field]);

                } 

            } 

        }

    }

Regards,

Marco

[Hariharan J V]

Hi Marco, 

Thanks for your update. 

We have validated your query. In Grid  If the column type is not defined, it will be determined from the first record of the dataSource. Incase if the first record of the dataSource is null/blank value for a column then it is necessary to define the type for that column. It is the default behavior of the Grid. From your code we colud see that first record was empty for the 3 datetime columns and also column type is not defined. This is the cause of the issue. So we suggest you to set the column type  or give the value for first record to resolve this issue. 

Please find the below documentation link for more information. 

Documentation Link: https://ej2.syncfusion.com/aspnetcore/documentation/grid/columns/#column-type 

Please get back to us if you need further assistance. 

Regards, 

Hariharan