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-database-using-aad-authentication/

Thanks!!

Antonio.

Need Help

aabalosc

9 years ago

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

v-haibl-msft

Microsoft Employee

9 years ago

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-support-for-odbc-date-and-time-improvements 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
Frequent Visitor
9 years ago
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
Frequent Visitor
9 years ago
Is there any missing part in the code? it does not seem to work.

Thanks,
Antonio.

aabalosc

Frequent Visitor

9 years ago

(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

v-sihou-msft
Microsoft Employee
9 years ago
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
  Frequent Visitor
  9 years ago
  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
    Microsoft Employee
    9 years ago
    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,

Forum Discussion

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

7 Replies

Related content

Copy Data to Fabric Warehouse – Handling nvarchar(max) and datetimeoffset(2) Columns

Data Pipeline truncate datetimeoffset with COPY activity

Power BI Report Server licensing with SQL Server 2025 Standard when separating servers

Power BI Report Server migration to new servers

Power BI Server