Sorting by date on custom reports

V5 Question
1

Is it possible to sort by date and time a Payments report?

This simple report:

[Dates:5,2, 2]
{REPORT PAYMENT DETAILS:P.Type, P.Amount, P.Date.asc}

shows the dates in order but it seems to be using the dates as strings. Thus, “1/3/2020” is smaller than “29/2/2020”

imagen

I have tried to reformat the date using FD but it does not work. I guess that asc and desc are useful for grouping and sorting on numeric fields but it seems that it uses the string representation of date and time fileds which makes it useless for effective sorting

Does anyone know the correct way to sort by date and time fields?

2

If not maybe look at having day and month as seperate fields sorted but hidden.

2 Likes
3

Using day of the month does not help when the working period is in between two months. For sorting correctly you need the entire date, maybe expressed as an number but TN won’t help either

4

That’s why I said both day and month.
Order columns with month then day and sort both.
Month sorted then day within month.

5

FD([P.Date],‘dd’).asc

try this