Forum Discussion

eliassal's avatar
eliassal
Helper II
6 years ago
Solved

Date/Time Issue between SQL and Power BI Desktop

Hi, I have a select statement that runs fine in SSMS it returns 1st column as date as follows   SELECT CAST(timestamp AS DATE) AS daily, Datepart(Year, Timestamp) as Year, Datepart(Month, Timest...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi eliassal ,

    Becasue date type in sql server is corresponded with date/time type in power bi desktop.

    If you want to use horizontal bar chart to show the date without time, I think you should contact the owner of this custom visual because this custom visual shows time automatically and we could not change it settings.

    One temporary way to use this visual to show date without time is to change the data type in cast() function from date to varchar:

     

    Or you can use default bar charts in power bi such as Stacked bar chart, change the format of daily field and change the type of Y-axis as Categorical:

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

simrantuli's avatar
simrantuli
Continued Contributor
6 years ago

Hi eliassal ,

 

How are you doing this transformation?

I recreated your scenario and changed the timestamp to Date in Power query and it worked fine for me. Didn't give me any errors.

 

 

 

However, if it still doesn't work for you, you could change the format of this column in 'Model' view. Please find below the screenshot.

Regards

Simran Tuli

  • eliassal's avatar
    eliassal
    Helper II
    6 years ago

    Power query, where in Power BI? I am confused, I thought that Power query is used in excel !!!

    • v-yingjl's avatar
      v-yingjl
      Community Support
      6 years ago

      Hi eliassal ,

      Based on my test, I can get the same result using the sql statement in power bi and need not change the data type of daily column as date type.

      You can open power query editors by choosing transforming data.

      You can try to paste your sql statement in the advanced options when connecting to sql server to check. Please note that ORDERBY cannot be quoted under direcr query mode.

       

      Best Regards,
      Yingjie Li

      If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

       

       

      • eliassal's avatar
        eliassal
        Helper II
        6 years ago

        Li, you are showing exactly the same thing, your text in the horizantal bar is showing date and time on each bar as mine which is my problem. This is exactly what I do, in the preview in powerbi it always shows date time I am not sure you have rad my initial post. I want to get rid of the zone time phrase, just want to see the date on the bar

        So I need iot to be

         

        instead of