Column sorting date doesnt sort accordingly

  • 14 November 2023
  • 2 replies

Badge +1

Hi, I'm fresh, sorry for any mistakes.

Usecase: Users want to column sort respective tables on date, this doesnt work as we intended. (Screenshot below post)

What doesnt work?  

  1. Datenotation(timezone) in the date domain (widely used) is set to local (Netherlands) but the grid still shows a MM/DD/YYYY Notation. 
  2. Sorting doesnt seem to work as intended. It drops a random 2022 row between a 2023 rows. 

What have I tried?

  1. Tried to set the datenotation(timezone) to ‘Show as stored’ and back to ‘Local’.
  2. Tried looking for hidden sorts on the grid, there isnt any.
  3. Checked if the date is stored as something different than a date (like string, int, etc)

When is my problem solved?
The moment sorting works again as intended, even better would be a small explanation why it does(n't) work.


Thanks in advance.



Best answer by Anne Buit 15 November 2023, 16:01

View original

This topic has been closed for comments

2 replies

Userlevel 7
Badge +5

Hi Dennis,

Does the order-by part in the query corresponding to the data selection shown in the database event log shed any light on the cause?


The DB event log. If this option is not present, ensure your account is configured for Developer Mode in IAM

You can execute the same query as shown in the database event log directly on the database and check if it shows the same ordering discrepancy.

Badge +1

It did and it confirmed what I thought. The date is being converted to a varchar, which changes the sorting from date to string. This line.

cast(convert(varchar(11), start_date, 105) as nvarchar) as sd


Now its just finding the source of the convert and fix that.