Sorting by date

[kaledev]

I am having an issue with a query. It is returning dates that have been formatted in mmm 'yy format, meaning Jan '11, Feb '11, and so on.



For whatever reason when sorted it relies on A-Z instead of Newest to Oldest and so on. Jan '11 is being displayed first, then Jan '10, then Feb '11, Feb '10...

I need this to be sorted like a regular date field, containing '11 data first, then '10. I tried using the Format function:

Format([Month], "yyyy-mm-dd")

And it just spits out Jan '11 again like I didn't do anything. Is there some sort of trick to sorting dates in mmm 'yy form? I have to keep them in mmm 'yy form unfortunately, otherwise I would change them all to normal dates. Thanks!

[SoftwareMatters]

Add another column with the date in the correct format and sort on that instead.

[ajetrumpet]

what is your code in the ORDER BY clause? It shouldn't do that naturally.

for instance, this code should work fine:

SELECT format(datefield, "mmm yy")

ORDER BY datefield

if you're ordering by the format itself, or by the alias name you've assigned to the expression, it will probably do what its doing, because the expression starts with an alpha, not a number. Moreover, the expression, which I believe is automatically assigned to an alias field name, is not the datefield.