Date slicer

[asbains8]

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 get the below message.

Error Message:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.. The exception was raised by the IDataReader interface.

My PBI local settinhg is set to English(UK) as is the server, database and user account connecting to the db???

[v-huizhn-msft]

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]

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]

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]

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

[v-huizhn-msft]

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]

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