DirectQuery SQL Convert datetime to date

[min-E]

Using DirectQuery to pull a SQL query

The format of the date column is datetime '2023-01-01 00:00:00.000'

In SQL if I use the CONVERT below, I get the results wanted:

SQL:  convert (date, (date))

Results:  2023-01-01

However, when I put my SQL into PBI, the date column is displayed in its original datetime format. 

Are there any solutions or workarounds that don't involve Import Mode?

Thank you for your help!!

[Alex_Sawdo · Accepted solution]

Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI. 

[vineet_nair · Accepted solution]

The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.

No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.

See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]

[min-E]

I am using a sql query directly in pbi. Good to know that it isn't an option this way. Thanks for your reply.

The only option may be to use 'import mode' and a scheduled refresh.

[Alex_Sawdo]

Have you tried to change the data type from Date/Time to Date in Power Query? You can do this by selecting the column and changing the data type in the transform tab. 

[min-E]

Hello,

Indeed I have. It displays the message 'This step is not supported in DirectQuery mode. Switch to import.'

[Alex_Sawdo · Accepted solution]

Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI. 

[min-E]

I am using a sp. Tested on an existing table and the outcome was what I needed.