Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Dates in text format - sql query connection.

team good morning,

I have problems with the dates of several sql tables already nourished information which needs to be changed to date format.

they are displayed as follows:

20211125111517346

understanding that it would be the date plus the hour minutes seconds and milliseconds.

Anyone know how to fix it? since from powerbi does not let me change the format because I am in query mode.

would be of great help.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Syndicate_Admin , if you using direct query my advice would be create a view and bring date and time from the database or write SQL if allowed in connection 

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Thanks for the reply,

      The problem is that I have access to the tables from PowerBI, but I do not have write access, I could see that the field is text, you can see that the date impacts from a Now() (it seems to me) since when impacting it is only text and in the following format with milliseconds:

      20211125111517346
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Anonymous,

        I'd like to suggest you do these in t-sql query(e.g. left, right function) to extract the date and time part then transfer data types:

        LEFT (Transact-SQL) - SQL Server | Microsoft Docs

        RIGHT (Transact-SQL) - SQL Server | Microsoft Docs

        BTW, the current power bi seems not support the millisecond(power bi data model table time values are ended to the second level), if you want to keep these values, you may need to create a new column to store them.

        Regards,

        Xiaoxin Sheng