Forum Discussion
Solved
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 vie...
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 🌀.
Zanqueta
Super User
Super UserHi 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 🌀.
