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...
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi asbains8,

The date field in SQL database is data type date, you create a slicer including the date field, right? You get the error message when you select any date greater than the 12th of each month in slicer? Please share more details for further analysis.

In addtion, I import data using direct query model, and filter the data using sql statement like the following query, it works fine.

Select * from HumanResources.Employee T where T.HireDate>'2011-12-31'

Best Regards,
Angelia

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