Forum Discussion
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 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???
6 Replies
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,
AngeliaHi 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.
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
