Direct Query SQL Statement Unable to Convert Datetime to Date Format

[cody_c]

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?

[v-sgandrathi]

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.

[Zanqueta · Accepted solution]

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:

https://learn.microsoft.com/power-bi/connect-data/desktop-directquery-about

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 🌀.

[cody_c]

Thank you for your response, @Zanqueta.  We tried using CAST and CONVERT at the SQL level, but to no effect.  But I was able to change the parameters from date format to datetime format (that Power BI was able to accept), and the report is now functional.  

[amitchandak]

@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