31 Replies Latest reply: Nov 18, 2016 4:28 PM by Mark Shannon

How to convert date format from strange to normal

Fei Xu Apr 12, 2012 4:38 PM

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!

How to convert date format from strange to normal

Jason Michaelides Apr 12, 2012 5:35 PM (in response to Fei Xu )

Can you post a sample of your source data? And exactly where on the server are you getting the data from?
Like Show 0 Likes (0)

Actions
- Re: How to convert date format from strange to normal
  
  Fei Xu Apr 12, 2012 5:49 PM (in response to Jason Michaelides)
  
  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.
  
  Sample.qvw 173.0 K
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How to convert date format from strange to normal
    
    Jason Michaelides Apr 12, 2012 6:33 PM (in response to Fei Xu )
    
    Can you post the xml file too?
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How to convert date format from strange to normal
      
      Fei Xu Apr 12, 2012 7:04 PM (in response to Jason Michaelides)
      
      Thank you for your time!
      
      CalData.pgo.xml 1.8 K
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: How to convert date format from strange to normal
        
        Jason Michaelides Apr 13, 2012 4:48 AM (in response to Fei Xu )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Stefan Bäckstrand May 7, 2012 5:00 AM (in response to Jason Michaelides)
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 5:01 PM (in response to Stefan Bäckstrand )
        
        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
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Steve Dark May 7, 2012 6:07 PM (in response to Fei Xu )
        
        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/
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 6:19 PM (in response to Steve Dark )
        
        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?
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 7:18 PM (in response to Steve Dark )
        
        For example, the Lastupdated for a particular document is 40E4099EDEE8DD7D. I am pretty sure it is in this year 2012. However no matter how I tried to convert 40E4099EDEE8DD7D to Decimal number and calculate the date, the result is not in 2012.
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Rob Wunderlich May 7, 2012 7:46 PM (in response to Fei Xu )
        
        Updating Steve's example:
        
        =Date((num#('40E4099E', '(HEX)')/86400)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY')
        
        -Rob
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 7:53 PM (in response to Rob Wunderlich )
        
        Thank you very much Rob but the result is 2004, not 2012.
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Rob Wunderlich May 7, 2012 8:58 PM (in response to Fei Xu )
        
        I can't comment on your expectations of the source data. But I believe the conversion is "correct". Today's date in hex unix timestamp is 4FA85E73.
        
        -Rob
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 10:49 PM (in response to Rob Wunderlich )
        
        Thank you very much Rob!
        
        Like Show 0 Likes (0)
        
        Actions
        
        How to convert date format from strange to normal
        
        Steve Dark May 8, 2012 6:17 AM (in response to Fei Xu )
        
        Sometimes application's timestamps are not actually representations of the time - just a unique string that has no value other than being a unique string.
        
        It should be possible to find out if that is the case for this file.
        
        - Steve
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Fei Xu May 7, 2012 7:47 PM (in response to Stefan Bäckstrand )
        
        Maybe they are not UNIX timestamp in hexadecimal. I couldn't use any converter to convert them to dec and then add to qlikview sciprts
        40E4067AD8153D10,
        40E406245C71C71C,
        FE37E43C8800759C.
        
        For example through this website:
        
        http://unixtimestamp.50x.eu/
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Mark Shannon Nov 18, 2016 11:21 AM (in response to Fei Xu )
        
        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')
        
        Like Show 0 Likes (0)
        
        Actions
Re: How to convert date format from strange to normal

René Verschuren Jul 5, 2013 7:58 PM (in response to Fei Xu )

You can convert these strange dates (actually the dates are 64 bit hexadecimals) by using a formula I posted in http://community.qlik.com/docs/DOC-4388.

René
Like Show 0 Likes (0)

Actions
- Re: How to convert date format from strange to normal
  
  Fei Xu Jul 7, 2013 4:56 PM (in response to René Verschuren)
  
  Thank you very much Rene.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How to convert date format from strange to normal
    
    Mikael Kristensen Mar 5, 2015 4:00 AM (in response to Fei Xu )
    
    Hi, did you manage to convert the number 'FE37E43C8800759C' using the link René posted? I cannot make it work...
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How to convert date format from strange to normal
      
      René Verschuren Mar 5, 2015 5:48 AM (in response to Mikael Kristensen)
      
      Hi Mikael,
      
      The most accurate decimal representation of hex value FE37E43C8800759C is -1.0000000000000000525047602552E300.
      This is a negative 1 with really a lot of zeros.
      Hex value FE37E43C8800759C is not a date.
      Enormously big values cannot be used in QlikView.
      http://www.binaryconvert.com/result_double.html?hexadecimal=FE37E43C8800759C
      
      Use the formula from post http://community.qlik.com/docs/DOC-4388 to convert hex values to decimals.
      Past the formula (look at file Convert64bit_HEXtoDEC.txt) into a variable.
      Use the formula in your script or dashboard in this way: $(eConvertHexToDec(LastUsed))
      LastUsed is a field containing a date as a hex value.
      
      Hex value 40E4570D1ABCDF01 equals to decimal 41656,409513889.
      This decimal represents date 2014-01-17 09:49:42 (YYYY-MM-DD HH:MM:SS).
      
      Open the QVW (Convert_double_precision_floating_point_HEX_to_DEC.qvw) for a working example.
      
      Best regards,
      René
      
      Like Show 1 Like (1)
      
      Actions
      - Re: How to convert date format from strange to normal
        
        Mikael Kristensen Mar 12, 2015 9:39 AM (in response to René Verschuren)
        
        Thx, it works great. Though I have one question:
        When I make a straight tabel with calculated dim using the converter and for expression a field like Documents, then I get all the documents, and that is all fine. On the other hand if switch around and use the field as dim and put the converter in the expression then something goes wrong I dont get all the documents. How can that be?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        René Verschuren Mar 12, 2015 11:24 AM (in response to Mikael Kristensen)
        
        Your problem might be caused be NULL values.
        Try to convert NULL values to a default date with use of the Alt function.
        For example:
        =Date (Alt ($(eConvertHexToDec(LastUsed)), 2))
        Value 2 is date 1-1-1900.
        
        When you calculate expressions on-the-fly, the performance could be bad.
        Put the conversion from hex date to decimal date into the loading script.
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Mikael Kristensen Mar 13, 2015 4:40 AM (in response to René Verschuren)
        
        thx it worked, but it did not really solve the issue. That is I get all the "-" shown with this date. Still I only have a few documents with the real date and the rest with dates is still not shown.
        
        The strange thing is if have both tables described above, and then select in the table where all is shown, then suddenly it also appear in the other table where it initial is not shown. Any good explanation for this?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        René Verschuren Mar 13, 2015 5:45 AM (in response to Mikael Kristensen)
        
        Please post your QVW file.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Mikael Kristensen Mar 13, 2015 8:19 AM (in response to René Verschuren)
        
        sorry, confidential data.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        René Verschuren Mar 13, 2015 8:32 AM (in response to Mikael Kristensen)
        
        Try to convert the hex dates with help of an online converter, for example: http://www.binaryconvert.com/result_double.html?hexadecimal=FE37E43C8800759C
        
        I think the hex dates causing your problem are not dates.
        Dates are decimals between 0 and say 50000.
        
        "-" means NULL value.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Mikael Kristensen Mar 16, 2015 5:03 AM (in response to René Verschuren)
        
        hmm, that should not be the problem. When I make the straight table with the dimension calculated using the convert variable, I do get all the dates and it looks fine, I get a long list, they are not "-", the hexadecimal code is solid.
        In the same qvw and sheet making another table just with the converter in the expression instead of as an calculated dimension, it only shows a few documents.
        The funny thing is that the two table are connected very fine, so if I qlik on the first table with the long list, it actually do show this one in the second table which initial did not show that document and date.
        
        I do not understand why there is this difference in the tables?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        René Verschuren Mar 16, 2015 7:10 AM (in response to Mikael Kristensen)
        
        Hi Mikael,
        
        I think it is a good idea to do the conversion in the script instead of doing the conversion in a straight table.
        Then it is easier to pinpoint the exact reason why you are getting strange results.
        
        Hope this will help.
        
        Best regards,
        René
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: How to convert date format from strange to normal
        
        Mikael Kristensen Mar 16, 2015 7:46 AM (in response to René Verschuren)
        
        Thanks, I tried this and for the straight tables it does not change anything whether I calculated it or use a field from the script. Though it enable to make a listbox and then I get all the documents and converted dates including null values for both fields.
        
        Like Show 0 Likes (0)
        
        Actions
- Re: How to convert date format from strange to normal
  
  Mark Shannon Nov 18, 2016 4:28 PM (in response to René Verschuren)
  
  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
  
  Like Show 0 Likes (0)
  
  Actions