Date from QVD not exactly the same as Source

[#1]

Hi,

I created a QVD from Oracle and it has a date field. The Original date showed 7/10/2013 10:20:11 AM.

It saved in QVD as 41465.43068287

So i converted it on the load using Date(CreatedDate,'MM/DD/YYYY HH:MM:SS TT') and showed 7/10/2013 10:07:11 AM

The retrieved date is missing 13 minutes.

Am I doing something wrong?

Thanks for the help

[rajeshvaswani77]

Hi,

Is there are time difference between your QlikView server that generates QVD's as well as the client you are using?

thanks,

Rajesh Vaswani

[#3]

No. I am using a personal edition. so all the files are in my local.

And i notice that on the initial load from QVD without date formatting, it will come in correctly. Then i update some of the attributes of other fields, and the re-load, then the date field now shows the numerical value. So then i go back and add the Date() formatting. So I am a bit confused.

I was trying to build logic for incremental load so if the date retrieved was incorrect then my basis(if i included time element ) could be wrong

(disclaimer : I am new to QV)

[rajeshvaswani77]

Hi Janet,

Could you use the attachment for understanding concept of incremental load.

Hope it will be helpful.

thanks,

Rajesh Vaswani

[#5]

thank you for the attachment.I am doing page #9.

Also,i just want to know if there is really an issue for QV not converting dates exactly. Since QV converts date fields into number format, is it due to rounding? Or is it my mistake?

[rajeshvaswani77]

Hi Janet,

Behind the date issue you are facing. Whats the lines of code? Could you please post?

thanks,

Rajesh Vaswani

[rbecher · Accepted solution]

Hi Janet,

you have to use the proper format codes:

=Date(41465.43068287,'MM/DD/YYYY hh:mm:ss TT')

respective:

=Date(CreatedDate,'MM/DD/YYYY hh:mm:ss TT')

Capital 'MM' means the month.. 😉

- Ralf

[#8]

thank you. this resolved my problem. I did not know this.

i am wondering how QV determines how to display the date, sometimes it will display the date as 41465.43068287 and sometimes it will display it as it was originally stored. Is there any guidelines on this?

[rbecher]

Date/Timestamp is usually stored as dual data type containing a number value and a text representation value (date formatted value).

Sometimes it can happen that only a number is stored. Then you can format it with Date() function or just format the number in chart as Date.

[#10]

Hi Ralf,

Is that recommended best practice for retrieving dates from QVD to always use a date() function to ensure proper date formatting and retrieval?

new to QV so i have been trying to read a lot of best practice data modeling and charting for QV.