Forum Discussion

aabalosc's avatar
aabalosc
Frequent Visitor
9 years ago
Solved

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 th...
Need Help
  • aabalosc's avatar
    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
v-haibl-msft's avatar
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's avatar
aabalosc
Frequent Visitor
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