How to convert date format from strange to normal

[NZFei]

Hello, I have created a qvw file to show qlikview server information. I have ServerName, Name, NoOfNamedCALs and so on. However for three fields LastUpdated,  LastUsed and ToBeDeleted, the value (should be date, aren't they?) are strange. They are 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. I have tried to set the number formats for them to Date but it didn't work. Are they some kinds of date formats? How can I convert them to normal formats? Many thanks in advance!

[Jason_Michaelides]

Can you post a sample of your source data?  And exactly where on the server are you getting the data from?

[NZFei]

I got the data from CalData.pgo.xml (Because I have set PGOAsXmlAlso=1 in settings.ini, I can have CalData.pgo in xml file). It is located in server document folder. Sample is attached. Thanks a lot.

[Jason_Michaelides]

Can you post the xml file too?

[NZFei]

Thank you for  your time!

[Jason_Michaelides]

I don't think I can be much help to be honest!  I can only guess that those strings are links to another record, mabe in another of the .pgo files.

Sorry mate,

Jason

[StefanBackstrand]

It's a UNIX timestamp in hexadecimal.

Conversion:

40E40633h = 1088685619

Excel formula (for example):

=(1088685619)/60/60/24+DATE(1970,1,1)

Result:

Thursday, 2004-07-01 14:40:19

http://fmdiff.com/fm/timestamp.html

[NZFei]

Thank you so much, Stefan!

How can I add any lines to QlikView Scripts to convert UNIX timestamp in hexadecimal to default date/time format? Is it possible?

Fei

[stevedark]

Building on Stefan's post, I presume the QlikView code would be:

Date((1088685619/60/60/24)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY') as [My Date Field],

The second date format specifier you can change to be whatever you want, but the first one must match the format of the 1970 date.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

[NZFei]

Steve,

Thank you very much!

The last thing is the date/time I get from CalData.pgo.xml looks like 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. How can I convert them to like 1088685619 ? Any way to do it in script?