Date-time format lost after loading from QVD

[dawgfather]

I am new to using a QVD file and have run into something interesting. I have a check in place to see if the QVD file exists and if it does not, then run a SQL query to load it and then save it to a QVD file.

My SQL query has a field that is a datetime field from what I can tell and when the SQL loads I get results like:

10/22/2016 8:00:00 PM

10/1/2016 10:00:00 PM

...and all this is great. It is as I want it. But when I next run the reload it loads that same file from the QVD but then my chart details change and I end up with the following:

42665.833333333

42644.916666667

Why does loading it from the QVD change/alter the output?

[Anil_Babu_Samineni · Accepted solution]

Sometimes it will happen

After reload the qvd you can use

Date(date#(datefield,'mm/dd/yyyy hh:mm:ss tt'),'required')

Or

Date(num(datefield),'required format')

[marcus_sommer]

I think you need to format your timestamp before storing the data into the qvd - maybe with a Preceding Load:

t:

load *, timestamp(datetime, 'MM/DD/YYYY hh:mm:ss TT') as DateTimeFormatted;

SQL select * From MySQL;

- Marcus

[dawgfather]

Thank you...this worked, but I still do not understand why it works when the data is first pulled but after loading from the QVD instead of SQL, it changes.

[Anil_Babu_Samineni]

Sometime, Qlikview frustrated at us. Anyway, I got same issue few months ago. I did same thing. Still i don't know what is the problem to find?

Can you confirm me, Who created that qvd? And if answer is yes, then are you sure before store to qvd do you have Date format of your field of date or number field?

If Yes, Share me the data which you have for ref