DateTimeOffset data type not understood by Power BI (ODBC Driver 13 for SQL Server)

[aabalosc]

Hi Power BI Community!!

I am using the ODBC Driver 13 for SQL Server to get data from an Azure SQL database. This database contains tables with DateTimeOffset columns.

When I import one of these tables into Power BI Desktop, the DateTimeOffset columns are not understood and are shown as «Binary» data.

Is there a way to convert these columns in Power BI to date values? Is this a bug in the ODBC driver? Am I missing something?

Here is the method I am using to connect to the Azure SQL database from Power BI.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/04/06/connect-from-power-bi-to-azure-sql-dat...

Thanks!!

Antonio.

[v-haibl-msft]

@aabalosc

I’ve got response from the Product Team.

This is by design. ODBC 3.x does not define a type which is compatible with DateTimeOffset. When we encounter a custom type via ODBC, we return the raw data as binary and it's up to the user to try to understand it. In the case of DateTimeOffset, the documentation for the format can be found at https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-sup... and here's some sample code which shows it being decoded:

 let
            DateTimeOffsetParser = BinaryFormat.ByteOrder(
                BinaryFormat.Record([
                    Year = BinaryFormat.SignedInteger16,
                    Month = BinaryFormat.UnsignedInteger16,
                    Day = BinaryFormat.UnsignedInteger16,
                    Hour = BinaryFormat.UnsignedInteger16,
                    Minute = BinaryFormat.UnsignedInteger16,
                    Second = BinaryFormat.UnsignedInteger16,
                    Ticks = BinaryFormat.UnsignedInteger32,
                    ZoneHours = BinaryFormat.SignedInteger16,
                    ZoneMinutes = BinaryFormat.SignedInteger16
                    ]),
                ByteOrder.LittleEndian),
            DateTimeOffset= DateTimeOffsetParser(data),
            AsText = Text.Combine({
                Text.PadStart(Text.From(DateTimeOffset[Year]), 4, "0"),
                "-",
                Text.PadStart(Text.From(DateTimeOffset[Month]), 2, "0"),
                "-",
                Text.PadStart(Text.From(DateTimeOffset[Day]), 2, "0"),
                " ",
                Text.PadStart(Text.From(DateTimeOffset[Hour]), 2, "0"),
                ":",
                Text.PadStart(Text.From(DateTimeOffset[Minute]), 2, "0"),
                ":",
                Text.PadStart(Text.From(DateTimeOffset[Second]), 2, "0"),
                ".",
                Text.PadStart(Text.From(DateTimeOffset[Ticks] / 100), 2, "0"),
                " ",
                if DateTimeOffset[ZoneHours] < 0 then "-" else "+",
                Text.PadStart(Text.From(Number.Abs(DateTimeOffset[ZoneHours])), 2, "0"),
                ":",
                Text.PadStart(Text.From(Number.Abs(DateTimeOffset[ZoneMinutes])), 2, "0")})
        in
            if data is null then null else DateTimeZone.FromText(AsText),
    Custom = Table.AddColumn(datetimeoffsettest_Table, "Parsed", each DateTimeOffset.FromBinary([value]))
in
    Custom

Best Regards,
Herbert

[aabalosc · Accepted solution]

Hi!! I have worked out some Power Query code to produce a function that can be easily applied to any "binary" column of DatetimeOffset data type. This simplifies the process of applying the logic to one or more columns. I hope it helps!!

(to use it, just copy it to a blank query in Power BI and then add a new column to the data by using the "invoke custom function" option)

let
    DatetimeOffsetParsing = (binaryInput as binary) =>

let
    DateTimeOffsetParser = BinaryFormat.ByteOrder(
                BinaryFormat.Record([
                    Year = BinaryFormat.SignedInteger16,
                    Month = BinaryFormat.UnsignedInteger16,
                    Day = BinaryFormat.UnsignedInteger16,
                    Hour = BinaryFormat.UnsignedInteger16,
                    Minute = BinaryFormat.UnsignedInteger16,
                    Second = BinaryFormat.UnsignedInteger16,
                    Ticks = BinaryFormat.UnsignedInteger32,
                    ZoneHours = BinaryFormat.SignedInteger16,
                    ZoneMinutes = BinaryFormat.SignedInteger16
                    ]),
                ByteOrder.LittleEndian),
    DateTimeOffset = DateTimeOffsetParser(binaryInput),
    AsText = Text.Combine({
                Text.PadStart(Text.From(DateTimeOffset[Year]), 4, "0"),
                "-",
                Text.PadStart(Text.From(DateTimeOffset[Month]), 2, "0"),
                "-",
                Text.PadStart(Text.From(DateTimeOffset[Day]), 2, "0"),
                " ",
                Text.PadStart(Text.From(DateTimeOffset[Hour]), 2, "0"),
                ":",
                Text.PadStart(Text.From(DateTimeOffset[Minute]), 2, "0"),
                ":",
                Text.PadStart(Text.From(DateTimeOffset[Second]), 2, "0"),
                ".",
                Text.PadStart(Text.From(DateTimeOffset[Ticks] / 100), 2, "0"),
                " ",
                if DateTimeOffset[ZoneHours] < 0 then "-" else "+",
                Text.PadStart(Text.From(Number.Abs(DateTimeOffset[ZoneHours])), 2, "0"),
                ":",
                Text.PadStart(Text.From(Number.Abs(DateTimeOffset[ZoneMinutes])), 2, "0")}),
    myDatetime = DateTimeZone.FromText(AsText),
    Result = myDatetime
in
    if binaryInput is null then null else Result

in
    DatetimeOffsetParsing

[aabalosc]

Is there any missing part in the code? it does not seem to work.

Thanks,

Antonio.

[aabalosc]

@v-haibl-msft

Thanks for your response. For some reason, I cannot make the code to work in Power BI by using a new query. Is any part of it missing or mispelled? (I have tried to change it slightly, but did not find a good solution for it)

Expression.SyntaxError: Token Eof expected.

Antonio.

[v-sihou-msft]

@aabalosc

I reproduced your issue. 

We have reported it internally. I suggest you use build-in Azure SQL connect to get data currently. 

Regards,

Simon Hou

[aabalosc]

Thanks Simon. The reason I am using ODBC is to use the existing Active Directory system to authenticate users. Is there a way of using the built-in Azure SQL connector with this type of authentication?? That would be better than the ODBC workaround.

So far, it looks to me that the built-in Azure SQL connector just allows for SQL authentication, which might be time consuming if there are many users to register/maintain in the database.

[v-sihou-msft]

@aabalosc

It's true. Currently we can only connect Azure SQL with SQL authentication. 

As we have reported this issue, hope it get fixed soon, we will keep you updated.

Regards,