I have some date fields in query subjects in my model with Framework manager that I've formatted with Cast statements to remove the timestamp etc. something like this:
CAST ([Database Layer].[table name].[field], DATE)
But when my users bring those fields into Query Studio and export them to Excel, they come into Excel as some format *other than* date. They usually come into Excel as "General" or "Custom" format. And when they are in Excel as a "General" format you can't sort them as date. Can this be fixed or is this one of those "black-box" things that Query Studio does that we don't have much control over?
The problem may not be Cognos. Excel is very persnickety on what it thinks are data types on spreadsheets it did not create. It does some funky gymnastics to decide, and in particular looks at the first several rows of data. If there is no date, or it is not in a format Excel will see as a date, it may well decide it's a string.
This is speculation on my part, based on my experiences. I could very well be wrong, and would open a ticket to ask IBM to see what they say.