Date Grouping does not sort correctly

Applications Grid / Reports
#1

I have an MSSQL table that includes a datetime field that is output to Scriptcase. The information contained is from the last 14 days and is very simple, the query is sorted by date and outputs correctly in the detail grid starting at the oldest date first.

I then create a Group under “Group By” with the “YYYYMMDD” rule.When I output the result as a summary or as a chart the date order then becomes incorrect, for example at the moment it is showing 1/05 onward to today and then continuing from 22/04 where it should be starting from 22/04 onward. I extended the date range to test this and it shows 1/04, 1/05, 2/04, 2/05 etc as the order so I am assuming it is ordering by the day only and not including the month.

This is occurring on more than one grid, anywhere I am summarising data the date order is incorrect. I’m hoping this is a very simple issue and I’ve just missed an option somewhere that someone can point me to. I’ve checked the forums and Scriptcase help but have not been able to find a solution.

#2

On further investigation Scriptcase seems to be ignoring date sorting and sorting Alphabetically/numerically instead. This is a real problem for me at the moment, I’m generating a lot of line charts to show trends which are invalid due to the date sorting issue so it’s becoming quite urgent that I resolve this.

Any suggestions are welcome.

#3

Hello NetMake,

Please inform the users in the forum how to set a cronological ordered display of information rather than a lexical ordering of the data.

Thank-you - Ken

#4

Can you post your query? I’m thinking the group by is being ignored by the order by clause. SQL Server processes group by first then order by.

#5

I realise this is an old thread but did anyone get anywhere with it? We still found the same problem in v8.

Create a grid with a date field. Group by the date in YYYYMM format. Detail view is fine, but Summary view looks like this…

01/2013
01/2014
01/2015
02/2013
02/2014
02/2015
etc

…but It should be…

01/2013
02/2013
01/2014
02/2014
01/2015
02/2015
etc

It’s sorting alphabetically. That would be OK if it did actually show as YYYYMM, but it’s showing as MM/YYYY. I think that’s the problem.