Forum Discussion
Direct Query SQL Statement Unable to Convert Datetime to Date Format
I am generating a report that uses three parameters for a SQL view in Direct Query mode: Start Date, End Date, and Site ID. The end goal is to allow the end user to select all three parameters to view their data on demand for the appropriate site and date range (based on WO_Target_Date).
The Site ID parameter works as intended, allowing me to select from an available list and run the SQL query. The issue is with date conversion. The SQL query runs based on the defined Start Date and End Date, and the parameters are in date format. I have brought in a date dimension table (DIMDATE) in Import mode. My goal is to bring in DIMDATE twice, binding one DATE field to the Start Date parameter, and binding the other DATE field to the End Date parameter.
When I bind the DIMDATE DATE field to the Start Date parameter, and drop the DATE field into a slicer, the Direct Query SQL statement is not able to run. Using 1/1/2024 as an example, I receive the following error:
OLE DB or ODBC error: [Expression.Error] We cannot convert the value #datetime(2024, 1, 1, 0, 0, 0) to type Date..
The parameters are in date format, as is the DATE field in the DIMDATE table. I believe the SQL query is unable to run because the WO_Target_Date field is in datetime format in the SQL database source. But I can't directly change the format of that column under "Transform Data" because it's in Direct Query mode. Using "CONVERT(DATE,WO_Target_Date)" in the SQL statement has no effect. I can connect PBI directly to the SQL view, which shows as date format as intended when run in SSMS, but Power BI continues to view the WO_Target_Date column in datetime format, regardless. Converting the parameters to datetime format doesn't solve the issue, prompting the same error. I can't create a new calculated column in PBI because I don't think that will solve the problem of allowing the SQL query to run, and it needs to be in Direct Query mode to use the parameters. How can I "convince" PBI to view this column in date format, rather than datetime?
Hi cody_c,
This issue occurs because DirectQuery does not allow changing the data type at the Power BI level—it must respect the source schema. Even if you use CONVERT(DATE, WO_Target_Date) in your SQL view, Power BI often still interprets the column as datetime because the provider sends it as such. When you bind a parameter of type Date to a column of type datetime, Power BI tries to cast internally and fails.Why the Error Happens
- Your parameter is Date type.
- The SQL column is datetime.
- Power BI attempts to compare #datetime(2024,1,1,0,0,0) to a Date parameter, causing the conversion error.
Two Practical Solutions
Option 1: Force Conversion in the SQL View
Modify the SQL view so that the column is truly DATE type, not just converted in the SELECT clause. For example:CREATE VIEW vw_WorkOrders AS SELECT CAST(WO_Target_Date AS DATE) AS WO_Target_Date, SiteID, OtherColumns FROM WorkOrders;Option 2: Change Parameter Type to DateTime
If you cannot alter the view, make your Power BI parameters DateTime instead of Date. Then, when filtering, truncate the time portion in the SQL query using CAST or CONVERT:WHERE CAST(WO_Target_Date AS DATE) BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE)Official reference:If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
4 Replies
cody_c , Try to have their datatype as datetime, if they are coming from a source, make itthe same very first time when Power Query changes the datatype
Hi cody_c,
This issue occurs because DirectQuery does not allow changing the data type at the Power BI level—it must respect the source schema. Even if you use CONVERT(DATE, WO_Target_Date) in your SQL view, Power BI often still interprets the column as datetime because the provider sends it as such. When you bind a parameter of type Date to a column of type datetime, Power BI tries to cast internally and fails.Why the Error Happens
- Your parameter is Date type.
- The SQL column is datetime.
- Power BI attempts to compare #datetime(2024,1,1,0,0,0) to a Date parameter, causing the conversion error.
Two Practical Solutions
Option 1: Force Conversion in the SQL View
Modify the SQL view so that the column is truly DATE type, not just converted in the SELECT clause. For example:CREATE VIEW vw_WorkOrders AS SELECT CAST(WO_Target_Date AS DATE) AS WO_Target_Date, SiteID, OtherColumns FROM WorkOrders;Option 2: Change Parameter Type to DateTime
If you cannot alter the view, make your Power BI parameters DateTime instead of Date. Then, when filtering, truncate the time portion in the SQL query using CAST or CONVERT:WHERE CAST(WO_Target_Date AS DATE) BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE)Official reference:If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Hi cody_c,
Thank you Zanqueta and amitchandak for your answer to the query.
I wanted to check if you had the opportunity to review the information provided by Community members. Please feel free to contact us if you have any further questions.
Thank you and continue using Microsoft Fabric Community Forum.
