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: 
NZFei
Partner - Specialist
Partner - Specialist

How to convert date format from strange to normal

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!

31 Replies
mshann01
Contributor III
Contributor III

For anyone needing to convert these dates in SQL Server, below is a formula to calculate the date portion.  Just replace c.LastUsed with your date field.  You'll notice the base date is not the UNIX base date of 1/1/1970 but Microsoft's base date.  c.LastUsed needs to be a Binary(8) field

DATEADD(day, 1e0 * (CAST(SUBSTRING(c.LastUsed, 6, 3) AS INT) / 2097152e0 / 536870912e0 + (SUBSTRING(c.LastUsed, 2, 4)&268435455) / 536870912e0 + 0.5e0) * POWER(2e0, (SUBSTRING(c.LastUsed, 1, 2)&32752) / 16 - 1022), '1899-12-30')

mshann01
Contributor III
Contributor III

Alternatively to Rene's formula, the below appears to work just as well but is much simpler:

if(Mid(LastUsed,1,2) < '80',
date((Num(NUM#(Mid(LastUsed,11,6),'(HEX)'),'(BIN)')/2097152/536870912+(Num(NUM#(Mid(LastUsed,3,8),'(HEX)'),'(BIN)') bitand 268435455)/536870912+0.5)*pow(2,(num(num(Num#(MID(LastUsed,1,4),'(HEX)'),'(bin)')) bitand 32752) /16-1022))
, '')
as LastUsed