Forum Discussion

ChrisMajestic's avatar
ChrisMajestic
Frequent Visitor
2 years ago

How to prevent automatic datatype conversion with database connection

I have Power BI Report Server (May 2023) pulling in data from an ODBC connection. One of the fields is a duration field in the format 00:00:00 (hours, mins, secs). Since the numbers look like a time ...
Power BI Desktop May 2023
ChrisMajestic's avatar
ChrisMajestic
Frequent Visitor
2 years ago

I tried that but converting it to text doesn't revert the numbers back what they were prior to the transformation. In other words the previous number was 20:21:34 which it converted to 8:21:34 pm. So when I try to convert it to text it keeps it as 8:21:34 pm in text form instead of 20:21:34. 

d_gosbell's avatar
d_gosbell
Super User
2 years ago

So this could also be an issue with type conversion in the ODBC driver. You could try doing the conversion to a string explcitly in a view or SQL query so that it's done by the source database engine.

  • ChrisMajestic's avatar
    ChrisMajestic
    Frequent Visitor
    2 years ago

    Sounds interesting. How do I do that?

    • d_gosbell's avatar
      d_gosbell
      Super User
      2 years ago

      It depends on what sort of database server you are pulling the data from. In SQL Server you could use the CONVERT() function with the optional 3rd parameter to specify the format. The DBA for your source database might be able to help with this.