cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aabalosc
Advisor

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
aabalosc
Advisor

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

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
7 REPLIES 7
Moderator v-sihou-msft
Moderator

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

@aabalosc

 

I reproduced your issue. 

 

22.PNG

 

33.PNG

 

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

 

Regards,

Simon Hou

aabalosc
Advisor

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

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.

 

Moderator v-sihou-msft
Moderator

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

@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,

v-haibl-msft Super Contributor
Super Contributor

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

@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
Advisor

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

@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.

aabalosc
Advisor

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

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

 

Thanks,

Antonio.

aabalosc
Advisor

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

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 48 members 1,128 guests
Please welcome our newest community members: