Forum Discussion

asbains8's avatar
asbains8
New Member
9 years ago

Date slicer

I am using a direct query to connect to my SQL database, i am using a date field which is of data type date to use in my slicer, however when i select any date greater than the 12th of each month i g...
asbains8's avatar
asbains8
New Member
9 years ago

Hi Angelia

 

Yes that is what i am experiencing, i am using direct query but not importing the data into Power BI but linking directly to my sql database. If i import the table into PowerBI then the slicer works fine without any issues. I have attached images of when it works and the error message i get when selecting a date of the 12th or greater. I have also attached na image showing the date slicer properties and the data type of the field i am using in the slicer which is date with the format being dd/MMMM/yyyy.

 

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi asbains8,

Yes, thank you for detail description clearly. It's fine  the date is less than 12th. So please go back to your database, and check if there is nvarchar data type. In date column data type is same and it will convert into datatime type when you import data, so it's normally.

Best Regards,
Angelia

  • asbains8's avatar
    asbains8
    New Member
    9 years ago

    the date column in SQL is of format date and not nvarchar.

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi asbains8,

      It may be a date format issue. Please set the the SQL Server same as system date format and check if it is fine. And verify the date in database are valid.  There are similar issues in this link. Please use T-SQL the following in SQL Server database and verify if still has the problem. If it is, it should be SQL Server data problem, please post the issue to SQL Server formula for professional support.

      Select * from Table A where A.HireDate>'2011-12-31'



      Best Regards,
      Angelia

      • potsi's avatar
        potsi
        Regular Visitor
        9 years ago

        Run into same problem & error message when tried to filter a view based on a date column. Data was fetched from Azure SQL DB using direct query. Data type of matching SQL DB column was date, format in English US (yyyy-MM-dd). 

         

        In our case it helped when we changed regional settings on PC to match format in SQL DB: Control Panel > Clock, Language, and Region > Region, Change date, time or number formats:

        • Region format changed to English US (was Finland)
        • Short date format changed to 'yyyy-MM-dd', matching Azure SQL DB format (was dd.MM.yyyy)

        After these changes, launched PowerBI and re-connected to Azure SQL DB via direct query. Now filtering based on date column works as expected.

         

        Br. Matti