In both tables and dynamic tables, when ordering by dates (by clicking on the column header of the table) , the fields are actually ordered by comparing the strings of the dates, and not the dates themselves. This works fine if all the dates take the default SQL format of YYYY-MM-DD (I think this is the same for all databases). However, if we have dates with different precisions (e.g., specifying only the month or only the year), or if we set the option $wgAmericanDates to true (this will make the dates show as "May 8, 2019", for example) the ordering is wrong.
To fix this an attribute must be added to the HTML for each date field. For dynamic tables, the attribute is "date-order". For normal tables, the attribute is "data-sort-value". Then the fields will be ordered by the values of these attributes and not from the fields text. Using the dates in format YYYY-MM-DD as values for these attributes should work fine. An example of ordering by dates is shown in the datatables library page (https://datatables.net/examples/advanced_init/html5-data-attributes.html).
The same happens with Datetimes fields, in this case, ordering by the strings with format YYYY-MM-DD HH:MM:SS should also work fine.