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

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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft 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