Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date values sometimes display as numbers

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.

3 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hi,

QlikView takes the date in the Julian format.

the no. which you are getting is also a date but its in the julian form.

To get the format of date follow the steps given below:

Go to Properties => Number Tab => Click on Override Document Settings => then Click on date.

Or

If you want at load time then,

You can create the master calender. The date function will give the date as you require.

Refere http://community.qlik.com/message/223140 to create the master calender

Hope this will help.

Regards,

Nilesh Gangurde

Not applicable
Author

Thanks Nilesh.

Already knew about the override document settings and that will work but is considered unacceptable by the project managers.

I will look into the master calendar idea and see if that works, though I'm pretty sure any drastic changes to our load scripts will be ruled unacceptable by the project managers.

The question that they still want answered is why do some dates work "properly" and some don't.

Miguel_Angel_Baeyens

Hi,

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?

Hope that helps.

Miguel