Date sorting issue

[7webcreator]

Hello,

I have a list that is sorted by the date element and then by a time element. However, I have noticed that some rows are not behaving as they should do. Please see the screenshot below. I have noticed that in MySql, the date of the records sometimes comes with a time and sometimes it doesn't. !By changing the time to 00:00:00, the sorting works. How can I correct the issue and stop having the timestamp coming with the date? I need the date without the time for this element.

Here are the settings:
MySQL table->giorno = datetime
Fabrik->element->giorno ->plugin is date
Fabrik->element->giorno->Formatting&Time->Show Time Selector=no
Fabrik->element->giorno->Store Date as UTC
Fabrik->element->giorno->List & Form Format = D j M y
Fabrik->element->giorno->List View Settings->Filter Type->Range
Fabrik->element->giorno->List View Settings->Filter Type->Exact Match= yes
Fabrik->element->giorno->List View Settings->Filter Type->Filter Data=Show All

Joomla 3.10.3
PHP version 7.4.25
Fabrik 3.10

Thank you for your help

 

[troester]

I can't see a reason why this element would add a time part.
Do you have some other element (other list) pointing to the same table/column with different element settings?

 

[7webcreator]

No, I only have copies of this list and two joints due to two repeat groups.

 

[troester]

A copy of a list IS pointing to the same database table.

 

[7webcreator]

Yes, that is correct. The lists are all pointing to the same database table. The main list also has the 2 left joints.

 

[troester]

So maybe the date with time is coming from an insert or update from one of the other lists?

 

[7webcreator]

How could that happen as all the other lists are a copy of the main list? Also, the records are always added using the main index. The other lists are for viewing purposes.

 

[troester]

No idea, I don't know your setups.
Can you see based on the data in the records with date-time part (in your screenshot all "future" dates?) where they may come from?
Or some effect with failed validations?
Or some custom code?
...

 

[7webcreator]

The only explanation I can bring is that I have added 3 new fields to this list (date, beginning and end times) to replace the date-time element that I have unpublished. Then, I have copied the records from the column DateTime to the column date.
I have entered a dozen new records into this list, and the date field displays now the correct format Y-m-d 00:00:00. Now, I have to correct the 3000 rows that have the incorrect date format. Is it a quick way to update the table with the correct format?

 

[troester]

In PhpMyAdmin backup your table!!!,
then try
UPDATE your-table SET your-date-column=date(your-date-column)

 

[7webcreator]

Thank you very much. It has worked like a charm.