DATE IN DD/MM/YYYY FORMAT - SORTING AND FILTERING ISSUE

[Deepak]

 Dear Sir,

In SFDATAGRID where one column has in DD/MM/YYYY format data, then sorting on the said column, Date is not showing in sorting mode.. Example if I have data as per below

DATE

01/04/2018

05/04/2018

12/05/2018

06/05/2018

25/07/2018

02/04/2019

After Sorting to Date, getting result as per below :

01/04/2018

02/04/2019

05/04/2018

06/05/2018

12/05/2018

25/07/2018

(Here sorting is considering only on day, not with month and year)

Kindly note that In my Database, Data for dates are stored in DATETIME Format pattern DD/MM/YYYY. 

What is the solution for that ? For filter also data is showing as per above format.

Please send me Sample Application where Date is in DD/MM/YYYY format.

Thanks

Deepak 

[Deepak]

Dear Sir,

If for said Column done the grouping, then data is also not showing in the said sorting method ex.

grouping record is showing in following manners.

01/04/2018

02/04/2019  ?????

05/04/2018

06/05/2018

12/05/2018

25/07/2018

Please check it and do the needful at the earliest.

Thanks

Deepak

[Susmitha Sundar]

Hi Deepak, 

 

Based on the provided information, we have checked the mentioned issue “DataGrid DateTimeColumn sorted by date only” and we unable to replicate the issue from our end, it is working fine in both Sorting and Grouping as expected. We have prepared sample for the same, 

 

Sample link: https://www.syncfusion.com/downloads/support/forum/147722/ze/DataGrid_WF_DateSorting852929744 

 

Please check the above sample and revert us if you still facing the same issue? If yes, please modify the sample based on your scenario and revert us back with the following details, 

 

·       Issue reproducing video (if possible) 

·       Syncfusion update version 

 

It will be helpful for us to check on it and provide you the solution at the earliest.     

 

Regards,
Susmitha S 

 

[Deepak]

Hi Susmitha,

Thanks for reply.

We have tried your solution, and found following issues.

1. After data fetch from Database ex. MYSQL Database, we have to again initialised  columns and set the data type for all the columns. Instead of this, directly set data from database with initialised columns and date must be as per the defined date format in selected query. So further there will be no additional codes.

2. While grouping this Date field, it is showing Date with Time, where as there is no time in the data set.

3. While filtering this Date field, it is showing Date with Time, where as there is no time in the data set.

You are requested to please check it and send us the fresh sample.

Thanks

Deepak

 

 

[Susmitha Sundar]

Hi Deepak, 

 

Thanks for your update. 

 

We could able to reproduce your reported issue. Currently, we are checking the feasibility to fix this issue from our end. We will validate and update you details on or before October 3, 2019. 

 

We appreciate your patience until then. 

 

Regards, 

Susmitha S 

[Deepak]

Hi Susmitha S,

Waiting for Reply as today is 4th Oct.

Please send me at the earliest.

Thanks

Deepak

[Mohanram Anbukkarasu]

Hi Deepak, 

We regret for the inconvenience caused.  

We are currently working on this with high priority. We will update you with details on 7^th October 2019 without any further delays. We appreciate your patience until then.  

Regards, 

Mohanram A. 

[Mohanram Anbukkarasu]

Hi Deepak, 

Thanks for your patience. 

We have checked the reported issue in your application and we have noticed that the date field in the underlying data is in string type and it is generated as a GridTextColumn. So that the sorting will be performed by considering the values as string and it is not sorted in the date order. Sorting will be performed properly in date order, only if the underlying values are in DateTime type and the corresponding column is generated as GridDateTimeColumn.  

Please ensure this is in your application and let us know if you require further assistance from us. 

Regards, 

Mohanram A. 

[Deepak]

Hi Mohanram,

Thanks for Your Reply.

Please note that this Sample is provided by Syncfusion (Sushmita on 23/09/2019 - https://www.syncfusion.com/downloads/support/forum/147722/ze/DataGrid_WF_DateSorting852929744 )

Also note that the said queries are generated from your sample.

You are requested to please check it.

I also have one more query for the same as per below :

1. If all Columns are Auto generated (We are not defining any column) in that case, Why DATETime Field is auto generated as a String ? 

Ex. In MYSQLDB one field is in DATETIME Type, but after auto generate, this field is generated as String.

2. After all columns auto generated, can we define and change any column to another type ?

ex. in above 1, if field are auto generated and one field is as String, can we change that single fields Type from String to Datetime Type. ? Or We have to define each and every field manually.

You are requested to please send us fresh sample, so that we can do the needful with the solution of above new two query.

Thanks

Deepak

[Mohanram Anbukkarasu]

Hi Deepak, 

Thanks for your update. 

We checked all your reported cases and queries.  

Query	Comments
If all Columns are Auto generated (We are not defining any column) in that case, Why DATETime Field is auto generated as a String ?   Ex. In MYSQLDB one field is in DATETIME Type, but after auto generate, this field is generated as String.   After all columns auto generated, can we define and change any column to another type ?  ex. in above 1, if field are auto generated and one field is as String, can we change that single fields Type from String to Datetime Type. ? Or We have to define each and every field manually.	We have checked the reported case in a simple application using MySql and we are able to reproduce the reported case in our end. On analysis we have found that the type of the date column in the DataTable is MySqlDateTime. We have only considered  DateTime and nullable DateTime types to generate GridDateTimeColumn. Due to this that column is generated as text column.   This can be changed to a GridDateTimeColumn using SfDataGrid.AutoGenaratingColumn event. But the sorting will not work as expected in such cases.
While grouping this Date field, it is showing Date with Time, where as there is no time in the data set.    While filtering this Date field, it is showing Date with Time, where as there is no time in the data set.	We have checked these queries from your previous updates.   This can be resolved by setting GridColumn. GroupMode as DataReflectionMode.Display and GridColumn.FilterMode as ColumnFilter.DisplayText.   However for DataTable the GroupMode property will not be work as expected.

 

We are currently working on these scenarios with high priority to find the feasibility to resolve these cases. We need two more working days to validate these cases properly. We will update you with further details on 10^th October 2019. We appreciate your patience until then.  

Regards, 

Mohanram A. 

[Mohanram Anbukkarasu]

Hi Deepak, 

Thanks for your patience. 

Query	Solution
Regarding the auto generation of DateTime column	We have checked the cause for this in your application. We suggest you to make the following changes in your application.  In the creation of select query remove the code to apply format for the date field (DATE_FORMAT(creation_date,'%d/%m/%Y') as `creation_date`) and specify the column as it is. While creating connection string in dbconnection.cs file use Convert Zero Datetime=True;  instead of using Allow Zero Datetime=True; As given below.  public dbconnection()  {  cs = "datasource=localhost; port=3306; username=root; password=coolcomp@123;command timeout=120;Convert Zero Datetime=True;";  }  Once the made the above changes, the specific column will be auto generated as GridDateTimecColumn and there is not need to set AdvancedFilterType as DateFilter for the specific column. So that you can remove those changes in your application.
Regarding the GroupMode property not working for DataTable.	We have checked this case in our end and currently GroupMode support is not provided for DataTable. We have considered this and we are planned to include this support. We will update you with the patch including this support on 31st October, 2019.

Please let us know if your need further assistance from us. 

Regards, 

Mohanram A. 

[Deepak]

Hi Mohanram,

Thanks for Your Reply.

Query	STATUS AS ON 17/10/2019
After data fetch from Database ex. MYSQL Database, we have to again initialised  columns and set the data type for all the columns. Instead of this, directly set data from database with initialised columns and date must be as per the defined date format in selected query. So further there will be no additional codes.	As per your suggetion of "Convert Zero DateTime=True" Function, getting Auto Data and Date column is working fine and Date Column Sorting Is also working fine. So this ID Query is solved and working fine.
While grouping this Date field, it is showing Date with Time, where as there is no time in the data set.	After giving above "Convert Zero DateTime=True" function, Still it is showing the same. As per your Reply, You will provide the patch on 31st October, 2019. You are requested to please do the needful at the earliest.
While filtering this Date field, it is showing Date with Time, where as there is no time in the data set.	After giving above "Convert Zero DateTime=True" function, Still it is showing the same. As per your Reply, You will provide the patch on 31st October, 2019. You are requested to please do the needful at the earliest.

You are requested to please do the needful at the earliest.

Thanks

Deepak

[Mohanram Anbukkarasu]

Hi Deepak, 

 

Thanks for your update. 

 

Query 1 : While filtering this Date field, it is showing Date with Time, whereas there is no time in the data set. 

 

You can change the behavior of displaying both date and time while filtering the date column by setting the GridColumn.FilterMode property as DisplayText as shown in the following code example.  

 

Code example :  

 

this.sfDataGrid1.Columns["ShippingDate"].FilterMode = Syncfusion.Data.ColumnFilter.DisplayText; 

 

Sample link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/147722_FilterModeSample122945978  

 

Query 2 : While grouping this Date field, it is showing Date with Time, whereas there is no time in the data set. 

 

As promised we have included GroupMode support for DataTable. Now you can change the behavior of displaying both date and time while grouping the date column by setting GridColumn.GroupMode as Display as shown in the following code example. 

 

Code example :  

 

this.sfDataGrid1.Columns["ShippingDate"].GroupMode = Syncfusion.Data.DataReflectionMode.Display; 

 

The patch for this can be downloaded from the following location.  

 

Recommended approach - exe will perform automatic configuration 

 

Please find the patch setup from below location: 

http://syncfusion.com/Installs/support/patch/17.3.0.14/1081926/F147722/SyncfusionPatch_17.3.0.14_1081926_10182019083623820_F147722.exe  

 

Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment 

 

Please find the patch assemblies alone from below location: 

http://syncfusion.com/Installs/support/patch/17.3.0.14/1081926/F147722/SyncfusionPatch_17.3.0.14_1081926_10182019083623820_F147722.zip  

 

Please find the Nuget from the below location:      

http://syncfusion.com/Installs/support/patch/17.3.0.14/1081926/F147722/SyncfusionNuget_17.3.0.14_1081926_10182019083623820_F147722.zip  

    

Assembly Version: 17.3.0.14     

 

Installation Directions :     

This patch should replace the file “Syncfusion.Data.WinForms” and “Syncfusion.SfDataGrid.WinForms” under the following folder.    

$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6    

Eg : $system drive:\Program Files\Syncfusion\Essential Studio\17.3.0.14 \precompiledassemblies\17.3.0.14 \4.6    

     

To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies. 

 

Note :  

You can change how you receive bug fixes by navigating to the following link and updating your preferences. 

 

https://www.syncfusion.com/support/directtrac/patches  

 

Disclaimer :  

Please note that we have created this patch for version 17.3.0.14 specifically to resolve the issue reported in this forum 147722. 

 

If you have received other patches for the same version for other products, please apply all patches in the order received. 

This fix will be included in our upcoming 2019 Volume 3 SP1 release which is expected to be rolled out in the mid of November 2019. 

 

Regards, 

Mohanram A. 

[Deepak]

Hi Mohanram,

Thanks for your reply.

After updating the patch, following is the status : 

Query 1 : While filtering this Date field, it is showing Date with Time, whereas there is no time in the data set. 

 

If Column is define separately and applying the given code then while filtering the Date is not showing Time, which is working fine as per requirement. But in Filter Box/Popup, the date is not sorted. After Applying Sorting in ascending/descending order, the sorting is not working. You are requested to check the sample you had send me.

Query 2 : While grouping this Date field, it is showing Date with Time, whereas there is no time in the data set. 

Its working Fine. But Default sorting of Date is as per arrow is in ascending where as data is in descending mode, and the vice versa. You are requested to check the sample you had send me.

New Query :

Query 3 : To Apply this code, I have to define the column, Whereas all the columns are auto generated at our side. And After Auto Generated all columns, to make the necessary changes of this column as per your suggestion is not working. So, requested to please implement this in defaults where columns are auto generated dynamically.

You are requested to please check it and do the needful.

Thanks

Deepak

[Mohanram Anbukkarasu]

Hi Deepak, 

Thanks for your update. 

Query	Solution
Query 1 : While filtering this Date field, it is showing Date with Time, whereas there is no time in the data set.      If Column is define separately and applying the given code then while filtering the Date is not showing Time, which is working fine as per requirement. But in Filter Box/Popup, the date is not sorted. After Applying Sorting in ascending/descending order, the sorting is not working. You are requested to check the sample you had send me.	We are able to reproduce these scenarios in our end. We are currently working on this with high priority. We will update you with details on 23rd October 2019. We appreciate your patience until then.
Query 2 : While grouping this Date field, it is showing Date with Time, whereas there is no time in the data set.   Its working Fine. But Default sorting of Date is as per arrow is in ascending whereas data is in descending mode, and the vice versa. You are requested to check the sample you had send me.
Query 3 : To Apply this code, I have to define the column, Whereas all the columns are auto generated at our side. And After Auto Generated all columns, to make the necessary changes of this column as per your suggestion is not working. So, requested to please implement this in defaults where columns are auto generated dynamically.	You can set the GroupMode and FilterMode for the column while autogenerating the columns by using the SfDataGrid.AutoGeneratingColumn event as shown in the following code example.  Code example :   this.sfDataGrid1.AutoGeneratingColumn += SfDataGrid1_AutoGeneratingColumn;    private void SfDataGrid1_AutoGeneratingColumn(object sender, AutoGeneratingColumnArgs e)  {      if (e.Column.MappingName == "ShippingDate")      {          e.Column.GroupMode = Syncfusion.Data.DataReflectionMode.Display;          e.Column.FilterMode = Syncfusion.Data.ColumnFilter.DisplayText;      }  }  Sample link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/147722_AutoGenerateSample-1278516561

  

Regards, 

Mohanram A. 

[Mohanram Anbukkarasu]

Hi Deepak, 

Thanks for your patience. 

Query	Solution
Query 1 : While filtering this Date field, it is showing Date with Time, whereas there is no time in the data set.        If Column is define separately and applying the given code then while filtering the Date is not showing Time, which is working fine as per requirement. But in Filter Box/Popup, the date is not sorted. After Applying Sorting in ascending/descending order, the sorting is not working. You are requested to check the sample you had send me.	We have checked this scenario and it is confirmed as a defect. We have logged a bug report on this regard. We will fix this issue and provide the patch for the fix on 6th November 2019.  Feedback link : https://www.syncfusion.com/feedback/9681/checkbox-items-in-filter-popup-are-displayed-in-incorrect-sort-order-when
Query 2 : While grouping this Date field, it is showing Date with Time, whereas there is no time in the data set.    Its working Fine. But Default sorting of Date is as per arrow is in ascending whereas data is in descending mode, and the vice versa. You are requested to check the sample you had send me.	We have checked this scenario and it is the expected behavior. Based on our architecture sort order on grouping will be decided based on the display value when the GroupMode is set to Display. So the sorting will be performed using the string values not by using actual date value. However you can resolve this by creating a custom sort comparer for the date column as shown in the following code example.  Code example :   this.sfDataGrid1.SortComparers.Add(new Syncfusion.Data.SortComparer() { Comparer = new CustomComparer(), PropertyName = "ShippingDate" });  public class CustomComparer : IComparer<object>, ISortDirection  {      public int Compare(object x, object y)      {          DateTime date1;          DateTime date2;            //While data object passed to comparer          if (x.GetType() == typeof(DataRowView))          {              date1 = (DateTime)((DataRowView)x).Row["ShippingDate"];              date2 = (DateTime)((DataRowView)x).Row["ShippingDate"];          }            //While sorting groups          else if (x.GetType() == typeof(Group))          {              //Calculating the group key length              date1 = DateTime.Parse(((Group)x).Key.ToString());              date2 = DateTime.Parse(((Group)y).Key.ToString());          }            else          {              date1 = (DateTime)x;              date2 = (DateTime)y;          }            //returns the comparison result based in SortDirection.          if (date1.CompareTo(date2) > 0)              return SortDirection == ListSortDirection.Ascending ? 1 : -1;            else if (date1.CompareTo(date2) == -1)              return SortDirection == ListSortDirection.Ascending ? -1 : 1;            else              return 0;      }      private ListSortDirection _SortDirection;        /// <summary>      /// Gets or sets the property that denotes the sort direction.      /// </summary>      /// <remarks>      /// SortDirection gets updated only when sorting the groups. For other cases, SortDirection is always ascending.      /// </remarks>      public ListSortDirection SortDirection      {          get { return _SortDirection; }          set { _SortDirection = value; }      }  }  Sample link : https://www.syncfusion.com/downloads/support/directtrac/general/ze/147722_CustomSort1949943201

Please let us know if you need further assistance from us. 

Regards, 

Mohanram A.