if(isnum(field), date(MakeDate(Left(field,4), num(Right(field,2)))),
)))) as field
Thanks for the explanation massimo.
I have tried 'Match' in the expression for sorting and got the right order for all the values as required.
=Match (expr, vlaue1, value2,......value10)
Value1 will be given index# 1, value2 will be given index# 2 and so on.
So, by keeping this expression in ascending order had worked for me.