DB2中如何按TO_CHAR(datefield,'MM-YYYY')实现正确升序排序？

Hey there, let's break down why your current sorting isn't working as expected and fix it!

The core issue here is that your mth column is a string formatted as MM-YYYY. When you sort strings, the database compares characters left to right—so 01-2020 gets placed before 11-2019 because the first character 0 is lex smaller than 1. That's not the chronological order you want.

Here are two straightforward solutions to resolve this:

Solution 1: Sort using the original date's year and month #

Instead of sorting by the string mth, use the actual date components from readingdate directly. This is typically more efficient since you avoid extra string-to-date conversion overhead:

select A,B,C,(TO_CHAR(readingdate,'MM-YYYY'))mth 
from TABLE1 
inner join TABLE2 on TABLE1.join_col = TABLE2.join_col -- Don't forget your join conditions!
left join TABLE3 on TABLE2.another_join_col = TABLE3.another_join_col 
where (Readingdate >= DATE ('2019-11-01') AND Readingdate < DATE ('2020-01-31') + 1 DAY) 
group by A,B,C,TO_CHAR(readingdate,'MM-YYYY') 
order by YEAR(readingdate), MONTH(readingdate);

Note: Adjust the year/month extraction functions to match your database:

• Oracle: Use EXTRACT(YEAR FROM readingdate) and EXTRACT(MONTH FROM readingdate)
• PostgreSQL: Use DATE_PART('year', readingdate) and DATE_PART('month', readingdate)
• SQL Server: YEAR(readingdate) and MONTH(readingdate) work as written

Solution 2: Convert the mth string back to a date for sorting #

If you prefer to use the existing mth column, convert it back to a date type in the ORDER BY clause. This tells the database to sort by chronological order instead of string order:

select A,B,C,(TO_CHAR(readingdate,'MM-YYYY'))mth 
from TABLE1 
inner join TABLE2 on TABLE1.join_col = TABLE2.join_col
left join TABLE3 on TABLE2.another_join_col = TABLE3.another_join_col 
where (Readingdate >= DATE ('2019-11-01') AND Readingdate < DATE ('2020-01-31') + 1 DAY) 
group by A,B,C,TO_CHAR(readingdate,'MM-YYYY') 
order by TO_DATE(mth, 'MM-YYYY');

Adjust the conversion function for your database:

• SQL Server: Use CONVERT(DATE, mth, 101) (or a style matching MM-YYYY)
• PostgreSQL/Oracle: TO_DATE(mth, 'MM-YYYY') works as written

Either approach will give you the desired chronological order: 11-2019 → 12-2019 → 01-2020.

内容的提问来源于stack exchange，提问作者max092012