none
How to convert decimal value 9.22337E+18 to datetime RRS feed

  • Question

  • How to convert decimal value 9.22337E+18 to datetime in power bi.

    Getting the error

    DataFormat.Error: We couldn't convert to DateTimeZone.
    Details:
        9.22337E+18


    Monday, February 5, 2018 12:38 PM

Answers

  • Ah, I see. You'll need to first replace those values with null. Before converting to Date/Time/Timezone, first right-click on one of the cells that contains 9.22337E+18, and choose Replace Values. Then enter null in the "Replace With" field. You should now see those values replaced with null, and you should be able to convert the column to Date/Time/Timezone.

    Ehren

    Wednesday, February 7, 2018 5:48 PM
    Owner

All replies

  • Hi there. What DateTime value are you expecting to get when you convert it?

    Ehren

    Monday, February 5, 2018 9:44 PM
    Owner
  • Any Format that should be datetime
    Tuesday, February 6, 2018 10:56 AM
  • What I mean is: what date/time is this value supposed to represent? (For example, is it August 3rd, 1987? Something else?)

    Thanks for clarifying.

    Ehren

    Tuesday, February 6, 2018 4:26 PM
    Owner
  • Thanks Ehren 

    In Power bi we choose the data source as Active Directory, from that we have expanded the user.accountExpires date in column.

    If the user account expires is empty then it display the column value as 9.22337E+18

    When we change the column value to date/time/timezone then its  throwing DataFormat.Error: We couldn't convert to DateTimeZone.Details: 9.22337E+18

    If the column value contains(user.accountexpires) 1.3152E+17 then its convert into 10/9/2017 12:00:00 AM +05:30

    so how to convert the value  9.22337E+18 into dd/mm/yyyy format or get the empty



    • Edited by hussain44 Wednesday, February 7, 2018 7:16 AM
    Wednesday, February 7, 2018 7:14 AM
  • Ah, I see. You'll need to first replace those values with null. Before converting to Date/Time/Timezone, first right-click on one of the cells that contains 9.22337E+18, and choose Replace Values. Then enter null in the "Replace With" field. You should now see those values replaced with null, and you should be able to convert the column to Date/Time/Timezone.

    Ehren

    Wednesday, February 7, 2018 5:48 PM
    Owner
  • "so how to convert the value  9.22337E+18 into dd/mm/yyyy format or get the empty"

    Just a note: You can use the function DateTimeZone.FromFileTime to convert the LDAP timestamp (Win32 FILETIME) to a date, time and zone, i.e.

    = DateTimeZone.FromFileTime(9.22337E+18)

    Wednesday, February 7, 2018 7:26 PM