I have several columns that return a DATETIME value out of a SQL Server 2008 R2 database.
All of the columns either return a valid SQL date (i.e. YYYY-MM-DD HH:MM:SS.SSS) or NULL.
Some of the columns display properly as a date (i.e. 2012-05-04 00:00:00.000 displays as 5/4/2012 12:00:00 AM), but others display as numbers (i.e. 2012-05-31 00:00:00.000 displays as 41060).
It seems like the more NULL data that is loaded the less likely QV is treat a date column as a date.
I understand that under the covers QV treats dates as numbers thus allowing for the data to be displayed in multiple ways. And I've seen that forcing the format might fix this (haven't had the chance to build a test app to prove that yet), but it's not much of an option for our solution (we allow a variable amount of columns and so rely heavily on LOAD *).
Any ideas on what might be causing this and how I might work around it?
If it helps we're using QV 10.0.4 on the server our application is running on.
It's always a good practice to use the LOAD sentence to control what you are putting into memory in addition to the SQL SELECT (what you are pulling from the database). In the LOAD sentence you can always use the Date() function to say QlikView to display that value with date format.
Or is that you are already using LOAD and Date() but it still shows some values as dates and some as numeric?